Try to use two functions in query

[Frances via AccessMonster.com]

Hi,
I need to use these two function in query and I don't know how. Cuz I'm a
Newbie.

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
Dim 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

Sub Test()

Dim strAString As String
Dim I As Integer
Dim intCnt As Integer

strAString = "This,calls,the,two,functions,listed,above"

'Find out how many comma separated words
'are present
intCnt = CountCSWords(strAString)


'Now call the other function to retrieve each one in turn
For I = 1 To intCnt
Debug.Print GetCSWord(strAString, I)
Next
End Sub


These functions breakdown a string into individual words forming a coloumn of
words.

 

[Dale Fye]

Frances,

It appears that what you want to be able to do is parse a string and have it
return a recordset ("a column of words"). You cannot do this in an Access
query.

Sorry, without a better explaination of exactly what you want to do, I
cannot recommend an alternate solution.

BTW, You can replace most of the code in your CountCSWords function by
using the following code. The left part of the equation counts the number
of characters in the string, and subtracts the number of characters in the
string when commas are replaced by an empty string (""). The right part
subtracts a minus one (adds one) to the value if the last character is not a
comma, and subtracts 0 if it is a comma, to account for a string where the
final character is a comma.

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

If VarType(s) <> 8 Or Len(s) = 0 Then
CountCSWords = 0
Else
CountCSWords = LEN(strTest) - Len(Replace(strTest, ",", "")) -
(Right(strTest, 1) <> ",")
Endif

End Function

HTH
Dale

 

[Frances via AccessMonster.com]

Thanks HTH
I do want to parse a string and make a column as the sub test illustrates, if
I can't do this as query how can I apply these functions to extract the
information from a table?

Frances,

It appears that what you want to be able to do is parse a string and have it
return a recordset ("a column of words"). You cannot do this in an Access
query.

Sorry, without a better explaination of exactly what you want to do, I
cannot recommend an alternate solution.

BTW, You can replace most of the code in your CountCSWords function by
using the following code. The left part of the equation counts the number
of characters in the string, and subtracts the number of characters in the
string when commas are replaced by an empty string (""). The right part
subtracts a minus one (adds one) to the value if the last character is not a
comma, and subtracts 0 if it is a comma, to account for a string where the
final character is a comma.

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

If VarType(s) <> 8 Or Len(s) = 0 Then
CountCSWords = 0
Else
CountCSWords = LEN(strTest) - Len(Replace(strTest, ",", "")) -
(Right(strTest, 1) <> ",")
Endif

End Function

HTH
Dale

Hi,
I need to use these two function in query and I don't know how. Cuz I'm a

[quoted text clipped - 57 lines]

of
words.

 

[Dale Fye]

Frances,

HTH stands for "Hope This Helps"

If you can give me a better idea what your data looks like, and what you are
trying to accomplish, I (or someone else on the newsgroup) might be able to
give you some suggestions. Explain what you want this portion of your
application to do.

Dale

Thanks HTH
I do want to parse a string and make a column as the sub test illustrates,
if
I can't do this as query how can I apply these functions to extract the
information from a table?

Frances,

It appears that what you want to be able to do is parse a string and have
it
return a recordset ("a column of words"). You cannot do this in an Access
query.

Sorry, without a better explaination of exactly what you want to do, I
cannot recommend an alternate solution.

BTW, You can replace most of the code in your CountCSWords function by
using the following code. The left part of the equation counts the number
of characters in the string, and subtracts the number of characters in the
string when commas are replaced by an empty string (""). The right part
subtracts a minus one (adds one) to the value if the last character is not
a
comma, and subtracts 0 if it is a comma, to account for a string where the
final character is a comma.

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

If VarType(s) <> 8 Or Len(s) = 0 Then
CountCSWords = 0
Else
CountCSWords = LEN(strTest) - Len(Replace(strTest, ",", "")) -
(Right(strTest, 1) <> ",")
Endif

End Function

HTH
Dale

Hi,
I need to use these two function in query and I don't know how. Cuz I'm
a

[quoted text clipped - 57 lines]

of
words.