filter by form - exclude null


I have a continuous form with a recordsource having 4 text fields total.  I'll call the recordsource  fields Field1, Field2, ....  In the form header I have 4 fields called field1filter, field2filter, ...   When a header filter field is Null I want to show all records (with respect to that field), null or not null, but when there is text in the respective filter field I want to show only records that are "like" the filter, without the nulls.  If I type text into a second "filter" field, I want to drill down the records to match the 2nd filter criteria as well, only records that match, no nulls with respect to the filtered fields.  So, with text in two of the filter fields I want to show only records that have matching text in their respective fields.

The data is originally pulled from an Excel sheet and put into the record source table but I was extremely diligent with my append queries extracting this data from the Excel sheet to make sure I have Nulls and not zero length fields or spaces instead of text.  I'm aware of the problems that null vs. 0 length or  spaces can cause.

Here is the criteria used in the form's query for the 4 filtered fields:
Field1 criteria:  Like "*" & [Forms]![<formname>]![field1filter] & "*" Or Is Null
Field2 criteria:  Like "*" & [Forms]![<formname>]![field2filter] & "*" Or Is Null
Field3 criteria:  Like "*" & [Forms]![<formname>]![field3filter] & "*" Or Is Null
Field4 criteria:  Like "*" & [Forms]![<formname>]![field4filter] & "*" Or Is Null

Basically, with respect to each filter, if I type text into a header filter I want to show matching records without null values, but if the filter is null, I want to show all records, including nulls.

Hope I explained correctly, any help on this is greatly appreciated.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

i.e. ((like a or a= null) and (like b or b = null) .. etc.

the code would be similar to:
if filter1 <> "" then strcriteria = "Field1 ='" & filter1 & "'"
if filter2 <> "" then strcriteria = strcriteria & " AND Field2 ='" & filter2 & "'"
if filter3 <> "" then strcriteria = strcriteria & " AND Field3 ='" & filter3 & "'"
if filter4 <> "" then strcriteria = strcriteria & " AND Field4 ='" & filter4 & "'"
cwbarrettAuthor Commented:
That seems like it would give me the same result but I may not understand your answer.  Could you show me how this would look in a query please?

When viewing a set of records in a table and you right-click a field and select "Equals "<currentfieldtext>" the records show only matching records for that field value (no nulls).  Then you go to another field and do the same it will show only records where both fields have a match.  But if you click "remove filter" in the menu all records show.  This is exactly how I want my filters to work except I want to type in the info into filter fields in my form header.
My code includes the 'and' operator - otherwise the same.
I was thinking of a button-triggered event on a form, and code
sub buttonclick
  dim strcriteria as string
  add the above code
  me.filter= strcriteria
end sub

let me know is this isn't clear - can you send a copy of the database?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

cwbarrettAuthor Commented:
is it possible to place an iif() function in the query criteria like:
iif(isnull([field1filter]), Like "*" & [Forms]![<formname>]![field1filter] & "*" Or Is Null, Like "*" & [Forms]![<formname>]![field1filter] & "*")

This will create one criteria if the value of the field1filter is null, and another criteria if it is not null.  But I don't know the proper way to place this in the query criteria.  

Could also "build" a criteria string in the form in a hidden text field called field1filterstring and place  "[Forms]![<formname>]![field1filter]" in the criteria.  Would you know the proper way to place this expression in the query criteria?
The sql approach may work but will be very difficult to construct with the brackets and syntax and debug.  Did you not want to do the button and code approach? You will still need a trigger to requery the data once all parameters have been selected.
Here is the way I do it:

Select ...
From ...
Where (field1 = Forms!yourform!filter1 OR Forms!yourform!filter1 Is Null)
AND (field2 = Forms!yourform!filter2 OR Forms!yourform!filter2 Is Null)
AND (field3 = Forms!yourform!filter3 OR Forms!yourform!filter3 Is Null)
AND ....

1. Since the compound expression uses both AND and OR, you MUST use parentheses to correctly control the order of evaluation.
2. This technique works whenever you want to support optional arguments.  If one or more of the arguments is not optional, remove the OR part of the expression.
3. This type of formatting is completely messed up by QBE.  So, NEVER, EVER save this query from QBE view or you won't be able to read the where clause.  When I use queries with this type of complex criteria, I make a copy of the query and label it with an _copy so if I accidentally open the main query with the QBE, I have a backup and won't have to retype the expression so I can read it.  Just for yourself, as long as you have a copy of the string formatted as above, go ahead and open it with QBE.  Notice all the lines of criteria and how complex they look.  Switch to SQL view and see the mess.  You'll get burned a few times but don't worry, you can always retype the where clause to be readable.
cwbarrettAuthor Commented:
I see what you're doing but won't this still show me nulls even with text in the respective form filter?  And now I now what you mean by complex and QBE rewriting the entire query on it's own.

I did find a fairly simple solution though.  In the Field row of the query, rather than put the table's field name, such as [location], I put this:  
LocationNoNull: IIf(IsNull([Location]),"",[Location])
This eliminated having to deal with Null results, results were a text string or a zero-length string, no nulls.  Then in the criteria row I put this:
Like "*" & [Forms]![ImportMain]![txtFilterLocation] & "*"

My original criteria originally had this which was necessary to show all values including null:
Like "*" & [Forms]![ImportMain]![txtFilterLocation] & "*" or is Null

Not sure how performance will be affected using an "iif" function in the query field but this is a transitional database that will probably have only a few thousand records max at one time.

Thanks for everyone's help.
When the filter is populated with a value, the selected record MUST match the value.  When the filter is null, essentially, the filter does not apply.  So if flilter 1 = 100 and filter 3 = 5, then ONLY records where field 1 = 100 and field 3 = 5 will be selected.  The contents of fields 2 and 4 are not relevant.

Like is not an efficient option since it can prevent the database engine from using an otherwise useful index.  Using LIKE, you almost always force the query engine to do a full table scan which is fine if the table contains 1,000 rows.  Not so fine if it contains 100,000 rows.

The only time you should ever use LIKE is if you actually have to process a partial text string.  If your criteria comes from combos, then it is always complete and LIKE should not be used.

In general, never use a function in a query if there is another option.  Again, whey your tables are small, there isn't much difference in speed but if you upsize to SQL Server or some other RDBMS, you must be much more cognizant of the use of functions in queries since not all functions can be sent to the server and you really don't want Access getting it into its head that it needs to request all the rows from a million row table just because you made a mistake and used a VBA or UDF query in a place you shouldn't have.

Personally, I never use ZLS and in fact set the table properties to not allow them.  ZLS doesn't apply to anything except string fields anyway so you always have the potential of nulls for dates or numeric columns unless you set them to be required.  Null is a one stop shop.  ZLS isn't.  What I don't ever want to have to deal with is the possibility of either ZLS or null in the same column which is why I force the issue by setting AllowZLS to no.  So, in every database I build,  If a field is not required, it could potentially be null.  As long as you understand that, you will handle nulls appropriately without a problem.  Nulls only get you if you let them.  Set required fields to required.  Sometimes you can use a default so the user doesn't need to actually enter a value.  On that note, think carefully about setting numeric fields to have a default of 0.  In most applications, 0 has a meaning.  If I took a test and my score was 0, that would be bad but if my score was null, it just means that the score is not yet entered.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cwbarrettAuthor Commented:
Thanks Pat, lots of great info here.  I see your point on the ZLS, they can be troublesome.  Though in this case ZLS was my friend and null was my enemy.  

Great point on the placing a function in a query field, I can see where that would cause problems.

I'm in the fire protection industry.  The app I made is a utility that draws records from various Excel sheets (linked tables) where I place into one local table.  The Excel sheets are from different manufactures each having their own way of identifying their product.  A Mfg's identifier might be "SMK", or "SMOKEDET" or "DETECTOR" and my job is to make them all "SMK DET".   Another example is "RELAY" (electrical relay).  The Identifier "RELAY" doesn't mean much, it's what the relay does that's important.  I determine what is does based on keywords in the other 4 fields.  I drill down to where I know all items in the list need to be a particular identifier.  Then using a query I update the identifier on each of the records to what is should be.  Rinse, repeat for other non-valid identifiers till all have a valid identifier.

Then I export to a master database (which is 100k plus records.) where it is related to a particular customer.  Then I clear the app's table and do another set of records for another customer.

If it took me an hour to "clean up" an Excel sheet or table prior to developing this app.  It now takes me 15 minutes.  And, I can train someone to use the app and I won't have to do it all!!

If you are going to allow ZLS, then it is best to make the field required so it won't ever be Null.  ZLS is "something" whereas Null is "nothing".

One other point about working with nulls.  When a foreign key can be null, you will have to use Outer Joins.  Usually Left but sometimes Right in order to not loose records.
cwbarrettAuthor Commented:
Thanks, I'm finally  understanding Null/ZLS.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.