Originally Posted by
June7
That's right, like the Allen Browne code. Construct criteria string with conditional code.
am using
Code:
Sub FilterRecords()
Dim StrState As String
Dim StrName As String
Dim StrPostCode As String
Dim StrSuburb As String
Dim StrPhone As String
Dim StrRemov As String
Dim StrID As String
Dim strConSearch As String
StrID = "[NewSchoolsID] Like ""*" & Me.txtIDnumber & "*"""
StrName = IIf(IsNull(Me.txtSchoolS), "", " and [SchoolName] Like ""*" & Me.txtSchoolS & "*""")
StrState = IIf(Not IsNull(Me.cmbState), " and [StateID] =" & Me.cmbState, "")
StrPostCode = IIf(IsNull(Me.txtFilterSchoolPostCode), "", " and [SchoolPostCode] Like ""*" & Me.txtFilterSchoolPostCode & "*""")
StrSuburb = IIf(IsNull(Me.txtSchoolSub), "", " and [SchoolSuburb] Like ""*" & Me.txtSchoolS & "*""")
StrPhone = IIf(IsNull(Me.txtPhone), "", " and [SchoolPhone] Like ""*" & Me.txtPhone& "*""")
StrRemov = " and IsNull([Removed])"
strConSearch = StrID & StrName & StrState & StrPostCode & StrSuburb & StrPhone & StrRemov
Me.frmSchoolSearchSub1.Form.Filter = strConSearch
Me.frmSchoolSearchSub1.Form.FilterOn = True
End Sub
however it is still not displaying records with empty fields...