string tokenizer 2

[hengsin]

Hi, can i know any function in VBA is equivalent to C++ string tokinizer?

Thanks

 

[jrbarnett]

Hengsin,

I can't take the complete credit for these routines - the originals are supplied with the Microsoft Access 97 Neat Code database in the String parsing module.
I have modified them to allow a custom delimiter to be specified, unlike the hard coded comma in the originals.

Create a new module in your database and copy and paste the following code samples in there. They let you delimit a string with different characters.

Public Function CountCSVWords(strString As String, strDelimiter As String) As Integer
'
' Counts words in a string separated by commas.
' Adapted from MS Neat Code 97 database by J Barnett. 

Dim WC As Integer, Pos As Integer
  WC = 1
  Pos = InStr(strString, strDelimiter)
  Do While Pos > 0
    WC = WC + 1
    Pos = InStr(Pos + 1, strString, strDelimiter)
  Loop
  CountCSVWords = WC
End Function

Public Function GetCSVWord(strString As String, Indx As Integer, strDelimiter As String)
'
' Returns the <Indx>th word from a <delimiter>-separated string.
' For example, GetCSVWord(&quot;Nancy, Bob&quot;, 2, &quot;,&quot;) returns Bob.
' Adapted from MS Neat Code 97 database by J Barnett.
'
Dim WC As Integer, Count As Integer, SPos As Integer, EPos As Integer

  WC = CountCSVWords(strString, strDelimiter)
  If Indx < 1 Or Indx > WC Then
    GetCSVWord = Null
    Exit Function
  End If
  Count = 1
  SPos = 1
  For Count = 2 To Indx
    SPos = InStr(SPos, strString, strDelimiter) + 1
  Next Count
  EPos = InStr(SPos, strString, strDelimiter) - 1
  If EPos <= 0 Then EPos = Len(strString)
  GetCSVWord = Mid(strString, SPos, EPos - SPos + 1)
End Function

You can then use the functions to extract items and the count function to work out the number of items within the string.

John

 

[TonyJollans]

Hi hengsin,

If you have Access 2000, take a look at the Split function. It will parse a string into a zero-based array using any delimiter you give it.

Enjoy,
Tony

 

[hengsin]

thanks, i used the combination split function and one-dimensional array to archieve what i want to do. Anyway, thank you very much. Giving you a star.

Lim Heng Sin