Filter fields including records with null

[Ruegen]

I have a multi search form that filters multiple fields using unbound text boxes

Me.frmSchoolSearchSub1.Form.Filter = "[NewSchoolsID] Like '*" & Me.txtIDnumber & "*'" & IIf(Not IsNull(Me.cmbState), " and StateID=" & Me.cmbState, "") & " and [SchoolName] Like '*" & Me.txtSchoolS & "*'" & "and [SchoolSuburb], Like '*" & Me.txtSchoolSub & "*'" & "and [SchoolPostCode] Like '*" & Me.txtFilterSchoolPostCode & "*'" & "and isnull(Removed)" & "and [SchoolPhone] Like '*" & Me.txtPhone & "*'"
Me.frmSchoolSearchSub1.Form.FilterOn = True

However if one or more of those fields happen to be blank then they won't show.

Is there a way to make a field if null still show?

[June7]

Two ways to handle:

1. Include Is Null in the criteria for each field

"and ([SchoolPostCode] Is Null Or [SchoolPostCode] Like '*" & Me.txtFilterSchoolPostCode & "*')"

2. Convert the null to a string with an expression in query and apply criteria to the calculated field

"and Nz([SchoolPostCode],"") Like '*" & Me.txtFilterSchoolPostCode & "*'"

[Ruegen]

Two ways to handle:

1. Include Is Null in the criteria for each field

"and ([SchoolPostCode] Is Null Or [SchoolPostCode] Like '*" & Me.txtFilterSchoolPostCode & "*')"

2. Convert the null to a string with an expression in query and apply criteria to the calculated field

"and Nz([SchoolPostCode],"") Like '*" & Me.txtFilterSchoolPostCode & "*'"

I don't think I can use option one because I don't want to show records just because they have a null (I want to say search a name, but if the name is missing say a post code then it won't show).

Option gives me a 3075 error unless I use ' ' instead of "" however it is still not giving me results.

The query record source works fine - just when filtering it doesn't.

[Ruegen]

As you can see in the image - there is a record with a missing name field.

If I filter the form id 9330 (green form) I get no result.

[June7]

I am confused. If you want to return record even if one of the data fields has null, must handle the null value with one of the two methods.

Also, don't include in criteria string if there is no value in the input.

Review: http://www.allenbrowne.com/ser-62code.html

[Ruegen]

I am confused. If you want to return record even if one of the data fields has null, must handle the null value with one of the two methods.

Also, don't include in criteria string if there is no value in the input.

Review: http://www.allenbrowne.com/ser-62code.html

Each textbox has an after update event

each one filters the whole subform continuous list - depending on what is in all the text boxes

so if there is say no text in textbox1 but text in textbox2 then it will filter the subform records to say "" in textbox1 and XYZ in textbox2 after the update

this is so the user can say start searching by a name, then narrow the list down to say postcode later

there is no end filter button - it searches as you tab fields or press enter on the keyboard.

However

If the record has no data in the field but has data in all the other searched fields it still won't show.

I type in the ID of the school in the image above and because it is missing a school name for that ID - the record won't show once filtered.

I tried nz([field] but I get a 3075 error

I am not sure on the concatenation or if this can be done.

I looked at the link - I see that it is multiple text boxes and one button that has a click event to filter the form to whatever the text boxes have, but that works differently to mine...

[June7]

Button click or AfterUpdate, code is the same, with minor adaptation. The procedure can be an unattached sub behind the form that is called by any event you want.

Sub FilterRecords()
...code
End Sub

Sub textboxname_AfterUpdate()
Call FilterRecords
End Sub

What is the exact message of the 3075 error? Show the exact code using Nz.

[Ruegen]

Button click or AfterUpdate, code is the same, with minor adaptation. The procedure can be an unattached sub behind the form that is called by any event you want.

Sub FilterRecords()
...code
End Sub

Sub textboxname_AfterUpdate()
Call FilterRecords
End Sub

What is the exact message of the 3075 error? Show the exact code using Nz.

yeah I guess I could do that (would look cleaner too)

I have (and please note all single ' have been replaced with " to accommodate adding ' in the text boxes)

[June7]

Part of the issue may be the double *.

Not including criteria if there is no value input might resolve this.

Couldn't hurt to try.

[Ruegen]

Part of the issue may be the double *.

Not including criteria if there is no value input might resolve this.

Couldn't hurt to try.

um... stupid question and refresher

how do I

"Not including criteria if there is no value input might resolve this."?

if isnull(me.txtbox) then etc?

[June7]

That's right, like the Allen Browne code. Construct criteria string with conditional code.

[Ruegen]

That's right, like the Allen Browne code. Construct criteria string with conditional code.

am using

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...

[Ruegen]

Update - it only seems to be when I search using the Me.txtIDnumber textbox...

and only if the school is missing SchoolName

[June7]

Is IDnumber a text or number type field?

[Ruegen]

txtIDnumber is General Number.... to avoid any character other than a number...