I went the detailed route where I am using If statements to add to the query code. I can add it below. I'm using unbound fields as I didn't want the data to be changed in the database. If I do bound each of the fields, how do I get it to where any update to that field doesn't update the database as well?
Code:
Private Sub cmdSearch_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.
'***********************************************************************
'Look at each search box, and build up the criteria string from the non-blank ones.
'***********************************************************************
'Text field
If Not IsNull(Me.H01) Then
strWhere = strWhere & "([H01] Like ""*" & Me.H01 & """) AND "
End If
'Yes/No field and combo
If Me.H02 = -1 Then
strWhere = strWhere & "([H02] = True) AND "
ElseIf Me.H02 = 0 Then
strWhere = strWhere & "([H02] = False) AND "
End If
'Yes/No field and combo
If Me.H03 = -1 Then
strWhere = strWhere & "([H03] = True) AND "
ElseIf Me.H03 = 0 Then
strWhere = strWhere & "([H03] = False) AND "
End If
'Yes/No field and combo
If Me.H04 = -1 Then
strWhere = strWhere & "([H04] = True) AND "
ElseIf Me.H04 = 0 Then
strWhere = strWhere & "([H04] = False) AND "
End If
'Number field
If Not IsNull(Me.H05) Then
strWhere = strWhere & "(H05] = " & Me.H05 & ") AND "
End If
'Text field
If Not IsNull(Me.H06) Then
strWhere = strWhere & "([H06] Like ""*" & Me.H06 & """) AND "
End If
'Text field
If Not IsNull(Me.H07) Then
strWhere = strWhere & "([H07] Like ""*" & Me.H07 & """) AND "
End If
'Date field
If Not IsNull(Me.H08) Then
strWhere = strWhere & "([H08] >= " & Format(Me.H08, conJetDate) & ") AND "
End If
'Number field
If Not IsNull(Me.H09) Then
strWhere = strWhere & "([H09] = " & Me.H09 & ") AND "
End If
'Number field
If Not IsNull(Me.H10) Then
strWhere = strWhere & "([H10] = " & Me.H10 & ") AND "
End If
'Date field
If Not IsNull(Me.H11) Then
strWhere = strWhere & "([H11] >= " & Format(Me.H11, conJetDate) & ") AND "
End If
'Text field
If Not IsNull(Me.H12) Then
strWhere = strWhere & "([H12] Like ""*" & Me.H12 & """) AND "
End If
'Date field
If Not IsNull(Me.H13) Then
strWhere = strWhere & "([H13] >= " & Format(Me.H13, conJetDate) & ") AND "
End If
'Text field
If Not IsNull(Me.H14) Then
strWhere = strWhere & "([H14] Like ""*" & Me.H14 & """) AND "
End If
'Date field
If Not IsNull(Me.H15) Then
strWhere = strWhere & "([H15] >= " & Format(Me.H16, conJetDate) & ") AND "
End If
'Text field
If Not IsNull(Me.H16) Then
strWhere = strWhere & "([H16] Like ""*" & Me.H16 & """) AND "
End If
'Date field
If Not IsNull(Me.H17) Then
strWhere = strWhere & "([H17] >= " & Format(Me.H17, conJetDate) & ") AND "
End If
'Text field
If Not IsNull(Me.H18) Then
strWhere = strWhere & "([H18] Like ""*" & Me.H18 & """) AND "
End If
'Number field
If Not IsNull(Me.H19) Then
strWhere = strWhere & "([H19] = " & Me.H19 & ") AND "
End If
'Text field
If Not IsNull(Me.H20) Then
strWhere = strWhere & "([H20] Like ""*" & Me.H20 & """) AND "
End If
'Date field
If Not IsNull(Me.H21) Then
strWhere = strWhere & "([H21] >= " & Format(Me.H21, conJetDate) & ") AND "
End If
'Text field
If Not IsNull(Me.H22) Then
strWhere = strWhere & "([H22] Like ""*" & Me.H22 & """) AND "
End If
'Text field
If Not IsNull(Me.H23) Then
strWhere = strWhere & "([H23] Like ""*" & Me.H23 & """) AND "
End If
'Text field
If Not IsNull(Me.H24) Then
strWhere = strWhere & "([H24] Like ""*" & Me.H24 & """) AND "
End If
'Yes/No field and combo
If Me.H25 = -1 Then
strWhere = strWhere & "([H25] = True) AND "
ElseIf Me.H25 = 0 Then
strWhere = strWhere & "([H25] = False) AND "
End If
'Yes/No field and combo
If Me.H26 = -1 Then
strWhere = strWhere & "([H26] = True) AND "
ElseIf Me.H26 = 0 Then
strWhere = strWhere & "([H26] = False) AND "
End If
'Text field
If Not IsNull(Me.H27) Then
strWhere = strWhere & "([H27] Like ""*" & Me.H27 & """) AND "
End If
'Date field
If Not IsNull(Me.H28) Then
strWhere = strWhere & "([H28] >= " & Format(Me.H28, conJetDate) & ") AND "
End If
'Text field
If Not IsNull(Me.H29) Then
strWhere = strWhere & "([H29] Like ""*" & Me.H29 & """) AND "
End If
'Date field
If Not IsNull(Me.H30) Then
strWhere = strWhere & "([H30] >= " & Format(Me.H30, conJetDate) & ") AND "
End If
'Text field
If Not IsNull(Me.H31) Then
strWhere = strWhere & "([H31] Like ""*" & Me.H31 & """) AND "
End If
'Text field
If Not IsNull(Me.H32) Then
strWhere = strWhere & "([H32] Like ""*" & Me.H32 & """) AND "
End If
'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere
'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub