multiple criteria search page (revised)

[Lauren B]

I have figured out how to search multiple fields using the following code:

DoCmd.OpenForm "Form1", acNormal, , "Field1 Like '*" & Me.Text1 & "*' AND
Field2 Like '*" & Me.Text2 & "*' AND Field3 Like '*" & Me.Text3 & "*'"

This code allows that user to search by any of these three criteria;
however, records with blank fields do not appear. For example, if a user
searches for all entries with the letter "A" in field 1, all entries with
the letter "A" in field will appear with the exception of those that have a
blank field 2 or field 3.

Is there a way to write my code so that entries with null fields will be
included?

Thank you for any assistance.

LB

 

[Alex White MCDBA MCSE]

Yes,

Personally I would not do things the way you have,

Dim SQL As String
Dim bolAnd As Boolean
Dim bolWhere As Boolean
bolWhere = False
Me.Recalc
bolAnd = False
SQL = "Select * from from TblCandidate "
If Len(Me.Title.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Title Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Title.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
Else
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Title Like '*' "
bolAnd = True
End If
If Len(Me.First_Name.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.First_Name Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.First_Name.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
If Len(Me.Surname.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Surname Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Surname.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
If Len(Me.Email_Address.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Email Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Email_Address.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
If Len(Me.Ad_Postcode.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Postcode Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Ad_Postcode.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
If Len(Me.Ad_County.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.County Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Ad_County.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
SQL = SQL & " Order By Surname"

DoCmd.OpenForm "Form1", acNormal
Form_Form1.recordsource = SQL
Form_Form1.requery


is the way I personally would solve your problem deals with the empty values
wildcard etc.