I have a library database with a search form that I'm working on that will allow users to search for multiple terms in a certain field (example: the user will be able to type in "marijuana OR pot" in the "Title" box of the search form and all records that have titles with either word in them will be returned).

This is turning out to be a LOT harder than I thought. However, I think I'm getting closer. My problem now is trying to link together the ideas I've collected into something that actually works.

As an example of what I've got that ISN'T working, here is the code that is supposed to process the Author information entered by the user in the search form. I'm trying to get it to take what was entered in txtAuthor on the search form and then see if there are any "and" or "or" restrictors in that entered phrase. If there are, I want the words between them separated out and the entire search string rephrased to go from this:

s.Author Like "Smith and Jones"

to this:

s.Author Like "Smith" and s.Author Like "Jones"

But my code isn't working. Does anything obvious stand out in this section?

If Not IsNull(txtAuthor) Then
answer = ""
Do
a = InStr(SearchStr, " and ")
o = InStr(SearchStr, " or ")

If a = 0 And o = 0 Then 'no ands or ors so 'just go on as usual
strWHERE = strWHERE & " AND s.Author Like '* " & txtAuthor & " *'"
Exit Function
End If

If a = 0 Or (o <> 0 And o < a) Then 'looking
'for ORs
answer = answer & "s.Author" & "=" & Chr$(34) & Left$(txtAuthor, o - 2) & Chr$(34) & " OR "
txtAuthor = Mid$(txtAuthor, o + 3)

Else 'look for ANDs
answer = answer & "s.Author" & "=" & Chr$(34) & Left$(txtAuthor, a - 2) & Chr$(34) & " AND "
txtAuthor = Mid$(txtAuthor, a + 4)
End If
Loop

strWHERE = strWHERE & " AND " & answer


End If

The ultimate goal is to come up with a long strWHERE statement that has the parameters from the Author, Title, and Source sections all linked together with ANDs (if the user has entered terms into all three fields).

The original function, which I am now doctoring up with the AND and OR stuff, looked like this:

Function BuildSQLString(strSQL As String) As Boolean

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
Dim strINTO As String

strSELECT = "s.* "


strFROM = "tblArticles s "

If Not IsNull(txtAuthor) Then
strWHERE = strWHERE & " AND s.Author Like '* " & txtAuthor & " *'"
End If

If Not IsNull(txtTitle) Then
strWHERE = strWHERE & " AND s.Title Like '* " & txtTitle & " *'"
End If


If Not IsNull(txtSource) Then
strWHERE = strWHERE & " AND s.Source Like '*" & txtSource & "*'"
End If


If Not IsNull(txtYear) Then
strWHERE = strWHERE & " AND s.Year " & cboYear & "'" & txtYear & "'"
End If
If Not IsNull(txtYear2) Then
strWHERE = strWHERE & " and " & "'" & txtYear2 & "'"
End If


strSQL = "SELECT " & strSELECT

If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)

BuildSQLString = True

End Function


Do I need to start over from scratch or can I alter this function the way I was trying to in the first example above?

This project has been making me crazy for about 8 months now. I need resolution or I'm going to lose it!

Thanks!

Meg Wood
knuckles@drizzle.com

Bellow is an answer for you. I only went as far as the Author and Title searches you can add the Source on by just coping one of the sections and adding it on. This is not perfect it does not take boolean precedence into account. That basically means this:

'Smith' or 'Weston' and 'Robert' = ('Smith' or Weston') and 'Robert'

Anyway I hope this helps.


Private Sub Command5_Click()
Dim stSQL As String
Dim stAuthors As String
Dim stTitles As String
Dim lAnd As Long
Dim lOr As Long
Dim stWhere As String

stSQL = "Select * from tblBooks "
If Nz(Me.txtAuthors, "") <> "" Then
stAuthors = "[Author] Like '*" & Me.txtAuthors & "*'"
lAnd = InStr(1, stAuthors, " and ")
Do While lAnd <> 0
stAuthors = Left(stAuthors, lAnd - 1) & "*' and [Author] Like '*" & Mid(stAuthors, lAnd + 5)
lAnd = InStr(lAnd + 5, stAuthors, " and ")
Loop
lOr = InStr(1, stAuthors, " or ")
Do While lOr <> 0
stAuthors = Left(stAuthors, lOr - 1) & "*' or [Author] Like '*" & Mid(stAuthors, lOr + 4)
lOr = InStr(lOr + 4, stAuthors, " or ")
Loop
End If
If Nz(Me.txtTitle, "") <> "" Then
stTitles = "[Title] Like '*" & Me.txtTitle & "*'"
lAnd = InStr(1, stTitles, " and ")
Do While lAnd <> 0
stTitles = Left(stTitles, lAnd - 1) & "*' and [Title] Like '*" & Mid(stTitles, lAnd + 5)
lAnd = InStr(lAnd + 5, stTitles, " and ")
Loop
lOr = InStr(1, stTitles, " or ")
Do While lOr <> 0
stTitles = Left(stTitles, lOr - 1) & "*' or [Title] Like '*" & Mid(stTitles, lOr + 4)
lOr = InStr(lOr + 4, stTitles, " or ")
Loop
End If
If stAuthors <> "" Then stWhere = " Where ((" & stAuthors & ") "
If stTitles <> "" Then stWhere = IIf(stWhere <> "", stWhere & " and (" & stTitles & ")", " Where (" & stTitles)
If stWhere <> "" Then stWhere = stWhere & ")"
Me.frmTableSub.Form.RecordSource = stSQL & stWhere
End Sub