split words separated by commas into array for criteria

[Santiago Gomez]

I would like to have one text box where the user can type any number of
words separated by commas, and then Split them into an array so I can create
WHERE statements to go with my query.

If I used the Split function though, it separates words by spaces, so a
valid 2-word search term will become useless as 2 separate words.

Something like this:

txtFilterCriteria : oranges, apples, orange cake

should split words into:
oranges
apples
orange cake

so that I can build a SQL statement :

If Me.txtFilterCriteria <> "" Then
strSQL = strSQL & " AND "
x = Split(Me.txtFilterCriteria)
For i = 0 To UBound(x)
strSQL = strSQL & "(a.[Notes #1]) " & sqlCriteria & "'*" & x(i) &
"*' " & sqlOperator & " "
Next i
'trim strSQL string to remove trailing OR/AND
While (Not (Right(strSQL, 1)) = "'")
strSQL = Left(strSQL, Len(strSQL) - 1)
Wend
End If

thanks

 

[Ken Snell]

You can specify the delimiter that the Split function should use.

Change your code line
x = Split(Me.txtFilterCriteria)
to this:
x = Split(Me.txtFilterCriteria, ",")

 

[Wayne Morgan]

Would using an In statement in your criteria be easier?

 

[Santiago Gomez]

cool, I didnt know about the delimiter. thanks Ken.

how would you use the IN statement? I've never used one. I need to search
the same field for matches like *apples* OR *oranges* OR *pineapple cake*

thanks.

Would using an In statement in your criteria be easier?

--
Wayne Morgan
Microsoft Access MVP

I would like to have one text box where the user can type any number of
words separated by commas, and then Split them into an array so I can create
WHERE statements to go with my query.

If I used the Split function though, it separates words by spaces, so a
valid 2-word search term will become useless as 2 separate words.

Something like this:

txtFilterCriteria : oranges, apples, orange cake

should split words into:
oranges
apples
orange cake

so that I can build a SQL statement :

If Me.txtFilterCriteria <> "" Then
strSQL = strSQL & " AND "
x = Split(Me.txtFilterCriteria)
For i = 0 To UBound(x)
strSQL = strSQL & "(a.[Notes #1]) " & sqlCriteria & "'*" & x(i) &
"*' " & sqlOperator & " "
Next i
'trim strSQL string to remove trailing OR/AND
While (Not (Right(strSQL, 1)) = "'")
strSQL = Left(strSQL, Len(strSQL) - 1)
Wend
End If

thanks

 

[Scott McDaniel]

Build your SQL statement as always, then add a WHERE clause similar to this:

WHERE YourFieldName IN ('Apples', 'Oranges', 'Peaches')

--
Scott McDaniel
CS Computer Software
Visual Basic - Access - Sql Server - ASP

cool, I didnt know about the delimiter. thanks Ken.

how would you use the IN statement? I've never used one. I need to search
the same field for matches like *apples* OR *oranges* OR *pineapple cake*

thanks.

Would using an In statement in your criteria be easier?

--
Wayne Morgan
Microsoft Access MVP

I would like to have one text box where the user can type any number of
words separated by commas, and then Split them into an array so I can create
WHERE statements to go with my query.

If I used the Split function though, it separates words by spaces, so a
valid 2-word search term will become useless as 2 separate words.

Something like this:

txtFilterCriteria : oranges, apples, orange cake

should split words into:
oranges
apples
orange cake

so that I can build a SQL statement :

If Me.txtFilterCriteria <> "" Then
strSQL = strSQL & " AND "
x = Split(Me.txtFilterCriteria)
For i = 0 To UBound(x)
strSQL = strSQL & "(a.[Notes #1]) " & sqlCriteria & "'*" &

x(i)

&