Generalizing a Parsing function 2

[timhans]

Hello, I have a parsing function which parse's with respect to a comma. If I go in to the module and change where the comma is declared and change it to a . then it will parse w.r.t a . similarly for a space or tab. So know I have three modules to parse three different ways. This is for a comma

'Create query like so, to use
'Toronto, Ontario, Canada Boston
'Field: City: GetCSWord([Location],1)
'Show: True
'Field: Region: GetCSWord([Location],2)
'Show: True
'Field: Country: GetCSWord([Location],3)
'Show: True

Function CountCSWords(ByVal S) As Integer
' Counts the words in a string that are separated by commas.

Dim 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

Function GetCSWord(ByVal S, Indx As Integer)
' Returns the nth word in a specific field.

Dim WC As Integer, Count As Integer, SPos As Integer, EPos As Integer
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


Is there a way to Make a single module and declare in calling the function which way to parse. I have tried

Function CountCSWords3(ByVal S, c As String) As Integer
' Counts the words in a string that are separated by commas.

Dim WC As Integer, Pos As Integer
If VarType(S) <> 8 Or Len(S) = 0 Then
CountCSWords3 = 0
Exit Function
End If
WC = 1
Pos = InStr(S, "c")
Do While Pos > 0
WC = WC + 1
Pos = InStr(Pos + 1, S, "c")
Loop
CountCSWords3 = WC
End Function

Function GetCSWord3(ByVal S, c As String, Indx As Integer)
' Returns the nth word in a specific field.

Dim WC As Integer, Count As Integer, SPos As Integer, EPos As Integer
WC = CountCSWords3(S)
If Indx < 1 Or Indx > WC Then
GetCSWord3 = Null
Exit Function
End If
Count = 1
SPos = 1
For Count = 2 To Indx
SPos = InStr(SPos, S, "c") + 1
Next Count
EPos = InStr(SPos, S, "c") - 1
If EPos <= 0 Then EPos = Len(S)
GetCSWord3 = Trim(Mid(S, SPos, EPos - SPos + 1))
End Function
It did not work get ambiguous name in query expression Thank's

 

[HarleyQuinn]

Yes, there is. The reason yours is not working is that you are checking for a literal c character rather than the variable you're passing your function. Try:

Function CountCSWords3(ByVal S, c As String) As Integer
      ' Counts the words in a string that are separated by commas.

      Dim WC As Integer, Pos As Integer
         If VarType(S) <> 8 Or Len(S) = 0 Then
           CountCSWords3 = 0
           Exit Function
         End If
         WC = 1
         Pos = InStr(S, [red]c[/red])
         Do While Pos > 0
           WC = WC + 1
           Pos = InStr(Pos + 1, S, [red]c[/red])
         Loop
         CountCSWords3 = WC
      End Function

I'd also investigate the Split() function for use in your function as it does exactly what you're after.

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[PHV]

A simpler way:

Function CountCSWords3(ByVal S, c As String) As Integer
' Counts the words in a string that are separated by c.
If Trim(S & "") <> "" Then
  CountCSWords3 = 1 + Len(S) - Len(Replace(S, c, ""))
End If
End Function{/code]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

You may also use this function and thus you don't need the above:

Function GetCSWord3(ByVal S, Indx As Integer, c As String)
If IsNull(S) Then Exit Function
Dim myArr
myArr = Split(S, c)
If Indx >= 1 And Indx <= (1 + UBound(myArr)) Then
  GetCSWord3 = Trim(myArr(Indx - 1))
End If
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HarleyQuinn]

I still think Split is easier...

Function CountCSWords3(ByVal S, c As String) As Integer
    CountCSWords3 = 1 + UBound(Split(S, c))
End Function

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[HarleyQuinn]

Yeah, combining the functions using Split() works well.

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[timhans]

There is a lots of great code here I get to study, I went with

Function GetCSWord3(ByVal S, Indx As Integer, c As String)
If IsNull(S) Then Exit Function
Dim myArr
myArr = Split(S, c)
If Indx >= 1 And Indx <= (1 + UBound(myArr)) Then
GetCSWord3 = Trim(myArr(Indx - 1))
End If
End Function

another question if your up for it, can this be altered so that it parse's w.r.t multiple dilimerters e.g , and .

Thanks HarleyQuinn and PHV