Wildcards, Commas, and Queries (newb alert)

[CrowdedBrain]

I'm frustrated by what I think should be simple SQL... here is the scenario.

Querying a field that is filled with 10 to 15 keywords, seperated by commas.

SELECT *
FROM research
WHERE keywords LIKE '%#URL.keywords#%'

URL.keywords is passed from the form the user submits.

Here is my delima, say the tables field contains "dog, texas, red". And my query is "red dog". I get nothing. Are the commas killing me? What am I missing? Let me know if I can better explain.

Thanks for any help you can lend.

CB

[GolferGuy]

You need to give your user the ability to search for "all" words, or search for the phrase. In your current procedure, you are searching for the phrase "red dog" To be able to find the record(s) you want, you will need to parse the "red dog" into two searches. You would search with "red" then search with "dog" I believe you can easily do this with one query, and put these two criteria in an "OR" relationship.

[Student007]

Try combining SQL string with multiple OR/AND conditions:

SELECT * FROM Research 
WHERE keywords LIKE "*red*" OR keywords LIKE "*dog*"

[CrowdedBrain]

Thanks for the suggestions, I'll start to work them in.

My follow up questions is this, if I am to split up the two words into two single word queries using either of the techniqes suggested, how do I split them when they are sent from a single text field?

[Student007]

 str = "a b c"
MsgBox Split(str)(2)

[GolferGuy]

Here is a function that will take the first word from your string of words and pass back that word. It also removes the word from the string before closing.

Function GetNextWord(inText)
'Define a work variable for use here
  Dim RetVal As Variant
'If the input text is blank, then exit the function now
  If inText = "" Then Exit Function
'Find the first blank space, which we are using as 
'   the delimiter between words
  RetVal = InStr(inText, " ")
'If there is a space found (RetVal > 0), then take out 
'   the first word and return that word.
  If RetVal > 0 Then
    GetNextWord = Left(inText, RetVal - 1)
'Change inText to remove the word we just used
    inText = Mid(inText, RetVal + 1)
  Else
'If there is no space found, then there is only one word
'  Set the return to that word, then remove it from inText
    GetNextWord = inText
    inText = ""
  End If
End Function

The way I see this function being used, is you would set up a routine to create the pieces of the WHERE clause, looping until this function returned a blank (="")

This will allow you to create the where clause with each word in the search text.

HTH,
Vic

[CrowdedBrain]

Excellent! Thanks guys, I'll see if I can get these to work.

CB