The Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. This method uses two user-defined functions: a function called CountCSWords () to count the number of words separated by commas. The following code would be useful for you if using the method that is explained above by 'Glenny'. The code should be like below :
Code:
CountCSWords Function (ByVal S) As Integer
'Counts the words in a string that are separated by commas.
Sun WC As Integer, Pos As Integer
If VarType (S) <> 8 Or Len (S) = 0 Then
CountCSWords = 0
Exit Function
End If
WC = 1
Pos = InStr (S, "")
Do While Pos> 0
WC = WC + 1
Pos = InStr (Pos + 1, S, "")
Loop
CountCSWords = WC
End Function
GetCSWord Function (ByVal S, Indx As Integer)
'Returns the nth word in a specific field.
Sun WC As Integer, Count As Integer, SPOS As Integer, As Integer Epos
WC = CountCSWords (S)
If Indx <1 Or Indx> WC Then
GetCSWord = Null
Exit Function
End If
Count = 1
SPOs = 1
For Count = 2 To Indx
SPOs = InStr (spos, S, "") + 1
Next Count
Epos = InStr (spos, S, "") - 1
If Epos <= 0 Then Epos = Len (S)
GetCSWord = trim (Mid (S, SPOS, EPOS - SPOs + 1))
End Function
Bookmarks