split words separated by commas into array for criteria

  • Thread starter Thread starter Santiago Gomez
  • Start date Start date
S

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
 
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, ",")
 
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.

Wayne Morgan said:
Would using an In statement in your criteria be easier?

--
Wayne Morgan
Microsoft Access MVP


Santiago Gomez said:
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
Click to expand...
Click to expand...
 
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
Santiago Gomez said:
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


Santiago Gomez said:
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 & "'*" &
Click to expand...
Click to expand...
x(i)
&
Click to expand...
Click to expand...
 
Back
Top