Boolean type Search

[susanmgarrett]

I need to create a user interface that will allow a boolean type search of a field. This would mean item1 AND item2 AND NOT item 3, item1 or item2, item 1 NOT item2, etc.

I've set up a combo box setup with the following script, but I can't figure out how to adjust it to allow the multiples.

Please advise if I should adjust this or try a differenmt interface format for my users.

__________________________________________________________

Option Compare Database 'Use database order for string comparisons
Option Explicit

Private Function AfterCombo(WhichLine As Integer)
Dim CBox As Control, TBox As Control, AndBox As Control, TBoxA As Control
Set CBox = Me("Combo" & WhichLine)
Set TBox = Me("Value" & WhichLine)
Set AndBox = Me("And" & WhichLine)
Set TBoxA = Me("Value" & WhichLine & "A")
TBox = Null
TBoxA = Null
Select Case CBox
Case "All", "Blank", "Not Blank"
TBox.Visible = False
AndBox.Visible = False
TBoxA.Visible = False
Case "Like", "Equal", "Less Than", "Greater Than", "Not Like", "Not Equal", "Not Less Than", "Not Greater Than", "In List", "Not In List"
TBox.Visible = True
AndBox.Visible = False
TBoxA.Visible = False
Case "Between", "Not Between"
TBox.Visible = True
AndBox.Visible = True
TBoxA.Visible = True
End Select
End Function

Private Sub Cancel_Click()
DoCmd.Close
End Sub

Private Function FormatList(ByVal List As String, FieldType As Integer)
Dim NewList As String, CommaPos As Integer, Word As String
NewList = ""
Do While Len(List) > 0
CommaPos = InStr(List, ",")
If CommaPos = 0 Then
Word = Trim(List)
List = ""
Else
Word = Trim(Left(List, CommaPos - 1))
List = Trim(Mid(List, CommaPos + 1))
End If
If Word > "" Then
Select Case FieldType
Case DB_TEXT, DB_MEMO
If InStr(Word, """") > 0 Then
MsgBox "Don't type double-quotes in the list"
End
End If
Word = """" & Word & """"
Case DB_DATE
If InStr(Word, "#") > 0 Then
MsgBox "Don't type '#' in your dates"
End
End If
If Not IsDate(Word) Then
MsgBox "Your list contains non-date characters"
End
End If
Word = "#" & Word & "#"
Case Else
If Not IsNumeric(Word) Then
MsgBox "Your list contains non-numeric characters"
End
End If
End Select
NewList = NewList & "," & Word
End If
Loop
NewList = Mid(NewList, 2)
If NewList = "" Then
MsgBox "Your list needs a valid value"
End
End If
FormatList = NewList
End Function

Private Function MakeNull(C As Control)
If Len(Trim(C)) < 1 Then C = Null
End Function

Private Function MakeSQL(WhichLine As Integer, FieldName As String, FieldType As Integer) As Variant
Dim CBox As Variant, TBox As Variant, TBoxA As Variant
Dim Condition As Variant, Delim1 As String, Delim2 As String
CBox = Me("Combo" & WhichLine)
TBox = Me("Value" & WhichLine)
TBoxA = Me("Value" & WhichLine & "A")
Select Case CBox
Case "Like", "Equal", "Less Than", "Greater Than", "In", "Not Like", "Not Equal", "Not Less Than", "Not Greater Than", "Not In"
If IsNull(TBox) Then
MsgBox "You have left a parameter blank for field [" & FieldName & "]"
End
End If
Case "Between", "Not Between"
If IsNull(TBox) Or IsNull(TBoxA) Then
MsgBox "You have left a parameter blank for field [" & FieldName & "]"
End
End If
End Select
Select Case FieldType
Case DB_TEXT, DB_MEMO
Delim1 = """"
Delim2 = """"
If Not IsNull(TBox) Then TBox = QFix(TBox)
If Not IsNull(TBoxA) Then TBoxA = QFix(TBoxA)
Case DB_DATE
Delim1 = "#"
Delim2 = "#"
Case Else
Delim1 = ""
Delim2 = ""
End Select
Select Case CBox
Case "All"
Condition = Null
Case "Blank"
Condition = " Is Null"
Case "Not Blank"
Condition = " Is Not Null"
Case "Like"
Condition = " Like """ & TBox & """"
Case "Equal"
Condition = "=" & Delim1 & TBox & Delim2
Case "Less Than"
Condition = "<" & Delim1 & TBox & Delim2
Case "Greater Than"
Condition = ">" & Delim1 & TBox & Delim2
Case "Not Like"
Condition = " Not Like """ & TBox & """"
Case "Not Equal"
Condition = "<>" & Delim1 & TBox & Delim2
Case "Not Less Than"
Condition = ">=" & Delim1 & TBox & Delim2
Case "Not Greater Than"
Condition = "<=" & Delim1 & TBox & Delim2
Case "In List"
Condition = " In(" & FormatList(TBox, FieldType) & ")"
Case "Not In List"
Condition = " Not In(" & FormatList(TBox, FieldType) & ")"
Case "Between"
Condition = " Between " & Delim1 & TBox & Delim2 & " And " & Delim1 & TBoxA & Delim2
Case "Not Between"
Condition = " Not Between " & Delim1 & TBox & Delim2 & " And " & Delim1 & TBoxA & Delim2
End Select
MakeSQL = " And [" + FieldName + "]" + Condition
End Function

Private Sub OK_Click()
Dim Where As String
Const ObType = "Form"
Where = Where & MakeSQL(1, "Lyrics", 10)
Where = Where & MakeSQL(2, "TrackTitle", 10)

On Error GoTo OKCApplyError
If Where <> "" Then
Where = Mid(Where, 6)
DoCmd.OpenForm "MasterFormQuery", , , Where
Else
DoCmd.OpenForm "MasterFormQuery"
End If

OKCExit:
Exit Sub

OKCApplyError:
MsgBox "Error " & Err & " opening " & ObType & Chr$(13) & Chr$(10) & Error
Resume OKCExit

End Sub

Private Function QFix(ByVal X)
Dim P As Integer
If IsNull(X) Then
QFix = Null
Exit Function
End If
P = InStr(X, """")
Do While P > 0
X = Left$(X, P) & """" & Mid$(X, P + 1)
P = InStr(P + 2, X, """")
Loop
QFix = X
End Function

Private Sub exitselectform_Click()
On Error GoTo Err_exitselectform_Click


DoCmd.Close

Exit_exitselectform_Click:
Exit Sub

Err_exitselectform_Click:
MsgBox Err.Description
Resume Exit_exitselectform_Click

End Sub

 

[KeithG]

why are you using a combobox? why not checkboxes? Can you explain a little more? Also try searching the forum for dynamic SQL I have posted some examples.

 

[susanmgarrett]

Search criteria

Keith - thanks for the help.

I'm building a database to do searches on a field that contains music lyrics. So one of my users might need a son that includes the words "love" and "blue" but not "sky" (yes, we do searches like that). The search terms change with every search.

I don't know how to do searches with check boxes, but I'll do a little more searching into dynamic sql as you suggest.

Regards,

 

[KeithG]

I think I might be leading you down the wrong path. Are you wanting to search multiple fields based on a user selection or search for multiple words in a sing field?

 

[susanmgarrett]

I'm searching for multiple words in a single field

 

[susanmgarrett]

Parameter Query?

I've been doing a bit more research and suspect that I may need to create a parameter query for this instance.

I can get

Like "*" & [Enter first search term:] & "*" And Like "*" & [Enter second search term:] & "*"

To give me the first search term and the second search term. I'm assuming I can use an IIF to continue to add search terms (IIF second term is not null add third term, if second term is null don't add third term). And I could do that as an OR statement.

My problem is that I need the user to be able to indicate whether the connection between each search term and the next search term is AND, OR, or NOT.

Any ideas?

 

[KeithG]

You need to build your SQL statement dynamically at runtime with the options your user chose.

 

[pbaldy]

I'm with Keith. Because of the dynamic nature of the And's & Or's, I think you're going to have to build the SQL dynamically. It will not be particularly hard, but it will be involved and will need to be well thought out ahead of time. You're also going to have to watch your parentheses, as there's a difference between:

(A And B) Or (C And D)

and

A And B Or C And D

 

[susanmgarrett]

Tutorial?

Thank you both very much.

Can you point me toward a tutorial on creating dynamic SQL, because I haven't the faintest idea where to begin.

I'm using Access 2003.

Thanks.

 

[pbaldy]

This is pretty good, and includes a sample db that shows one way to do it.

http://www.mdbmakers.com/forums/showthread.php?t=4895