Again, query where dealing with empty fields (2 Viewers)

[Geirr]

Hi all.
I'm sorry if I am bringing up probably an old and answered issue, but I just can't get things to work properly, and I cannot find a solution while trying to search for it.

I have a table with 5 column. For the sake of argument, - Field01 - Field05, all text fields. I do have a form with format set to Continuous Forms, and the record set to a query which held all fields. Secondly, in the from header - I have a three textbox (txtCriteria1 - txtCriteria3) which are used as a search criteria for the form, Field01, field02 and field03 where in the query I am using the standard (but not the best practice) criteria: Like "*" & [Forms]![IOListNavForm]![NavigationSubform].[Form]![txtCriteria1] & "*" (and ...[txtCriteria2], ...[txtCriteria3] for Field02 and Field03)
This works as a charm, but on records which does not have data in the field Field01 -02 and -03, I got all the records matching the criteria and also the records with empty value.

How can I show every records unfiltered, but only those who match the filter when filtered?
I found a hint in this forum from 2007; - iif(Forms!myform!SearchBox = "",True,mytable.TextField is like Forms!myform!Searchbox & "*") - but I just don't understand where and how to us it in the record source query. I'm just got totally blinded on this one.

I'm thankful for all help!
Brg, Geirr.

 

[theDBguy]

Unfortunately, the "Like" operator ignore null values. To include them, you'll have to use something like:

Like "something" AND FieldName Is Not Null

 

[arnelgp]

you can also try (for Field01):

 Like "*" & Nz([Forms]![IOListNavForm]![NavigationSubform].[Form]![txtCriteria1], [Field01]) & "*"

put the same on the other 2 fields.

 

[Geirr]

Unfortunately, the "Like" operator ignore null values. To include them, you'll have to use something like:

Like "something" AND FieldName Is Not Null

Hi.

Thank you for the answer, but the case is: Include all records (also empty data in the criteria field) when not filtered, and include only records with the selected criteria without empty data records when filtered. Tried with both Is Null and Not Is null. With And Is Not Nul I will never see the records wit empty data in the Field01 in my example.

Brg. Geirr

 

[Geirr]

you can also try (for Field01):

 Like "*" & Nz([Forms]![IOListNavForm]![NavigationSubform].[Form]![txtCriteria1], [Field01]) & "*"

put the same on the other 2 fields.

Hi.
Thank you for your answer. I was struggling with this one until I saw that I have to use semicolon instead of comma after the [Ctriteria1] above. But sadly, it gives only records with data in Field01, so empty fields records are still left out when not filtered.

Brg. Geirr

 

[arnelgp]

you do this same Criteria with Field02 and Field03:

Field01 
Criteria: Like "*" & Nz([Forms]![IOListNavForm]![NavigationSubform].[Form]![txtCriteria1], [Field01]) & "*" 

Field02
Criteria: Like "*" & Nz([Forms]![IOListNavForm]![NavigationSubform].[Form]![txtCriteria2], [Field02]) & "*"

Field03
Criteria: Like "*" & Nz([Forms]![IOListNavForm]![NavigationSubform].[Form]![txtCriteria3], [Field03]) & "*"

 

[Geirr]

you do this same Criteria with Field02 and Field03:

Field01
Criteria: Like "*" & Nz([Forms]![IOListNavForm]![NavigationSubform].[Form]![txtCriteria1], [Field01]) & "*"

Field02
Criteria: Like "*" & Nz([Forms]![IOListNavForm]![NavigationSubform].[Form]![txtCriteria2], [Field02]) & "*"

Field03
Criteria: Like "*" & Nz([Forms]![IOListNavForm]![NavigationSubform].[Form]![txtCriteria3], [Field03]) & "*"

Thank you, but As I wrote on your initial replay, this does not show records where there is no data and it's not filtered.
Attached pictures shows the criteria in the 'field01' in true life is Rev (Query_Criteria). The two other images shows output when the query has the criteria set, and without criteria set. The View_NoCriteria shows the first six record with empty data (criteria field in the query is empty), and the image View_Criteria shows the rows where all records with empty data is missing(criteria field has the criteria showin in the Query_Criteria.png), and in both view - the filter (fltRev) is not set (should show all records).

Brg. Geirr

 

[arnelgp]

you can also create a Public function in a Module and called it as Criteria on your Query.
In a Module paste this:

Public Function fnCriteria(ByVal fieldValue As Variant, ByVal txtBoxValue As Variant) As Boolean
txtBoxValue = txtBoxValue & ""
fieldValue = fieldValue & ""
If Trim$(txtBoxValue) = "" Then
    fnCriteria = True
Else
    fnCriteria = (fieldValue = txtBoxValue)
End If
End Function

on your Query Criteria (SQL View):

SELECT * FROM yourTable
Where fnCriteria([Rev], Forms]![IOListNavForm]![NavigationSubform].[Form]![txtCriteria1]) = True

 

[Geirr]

you can also create a Public function in a Module and called it as Criteria on your Query.
In a Module paste this:
...

Thank you very much!
I will look into this and try it out

Brg. Geirr.

 

[arnelgp]

here is a demo with a function as criteria on Query1.
open Module1 in vba and see the code.
Open Form1 and enter any value on unbound textbox and press the filter/unfilter button.
erase the content of the textbox and press the filter/unfilter button

 

[theDBguy]

Hi.

Thank you for the answer, but the case is: Include all records (also empty data in the criteria field) when not filtered, and include only records with the selected criteria without empty data records when filtered. Tried with both Is Null and Not Is null. With And Is Not Nul I will never see the records wit empty data in the Field01 in my example.

Brg. Geirr

You could try:
WHERE (Fieldname Like '*' & searchbox '*' And Fieldname Is Not Null) Or searchbox Is Null

 

[Geirr]

You could try:
WHERE (Fieldname Like '*' & searchbox '*' And Fieldname Is Not Null) Or searchbox Is Null

Thank you!
This works perfectly for my use

Brg, Geirr.

 

[Pat Hartman]

When criteria is optional, you must use the OR as the concatenation operator to handle the optional situation. Syntax is:

Where (fld1 = Forms!yourform!fld1 OR Forms!yourform!fld1 Is Null) AND (fld2 = Forms!yourform!fld2 OR Forms!yourform!fld2 Is Null) AND (fld3 = Forms!yourform!fld3 OR Forms!yourform!fld3 Is Null)

The parentheses are ESSENTIAL whenever your criteria includes both AND and OR.

If you realllllly need the LIKE with the wildcard, use it but the = is far more efficient if you are not actually looking for partial values.

 

[theDBguy]

Thank you!
This works perfectly for my use

Brg, Geirr.

Glad to hear you got it sorted out. Good luck with your project.