Refining criteria in MS Access query to allow nulls to flow through when using "*" in filter

I am using an MS Access query with filtered with criteria from a combo box.  I have a field call candidate in my table of 100 rows but not all of the fields have a candidate.  60 records have a candidate name and 40 records are blank. In my combo box (cbocandidateFilter) I default the value to "*" and would like the query to bring back all records (100 records)regardless of whether there is a value or not.  The problem is that it will bring back ALL records that are  NOT empty (60 records).  This is what I have in the criteria:  I use the NZ thinking it will allow nulls to flow through also but it does not.  Any suggestions?

Like (Nz([Forms]![frmHCMBP_ApprovalScreen]![cboCandidateFilter] & "*"))
marku24Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Mark,

Null is "no value" ... as such if the field has no value, it cannot be compared to anything.  If you are using the query designer and the field is text, in the Field cell, use:

nz( [myFieldname], "")  

then the Like operator in the criteria should work too. The way your expression is written, whatever is typed has to be at the beginning -- is this what you want?

If this query is feeding a report, there are other ways to get criteria in as well -- for instance, by specifying the Where clause prarameter of DoCmd.OpenReport

have an awesome day,
crystal
1
mlmccCommented:
You could also use something like this

([YourField]  Is Null OR [YourField] LIKE [Forms]![frmHCMBP_ApprovalScreen]![cboCandidateFilter])
0

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
marku24Author Commented:
I am using the result of the query to refine a list box.  In the query designer criteria row I used, Like Nz([Forms]![frmHCMBP_ApprovalScreen]![cboCandidateFilter],"") & "*" but it still only brings back all the data rows that have a value in that field.  I would assume if the value is null this criteria would be "*" and bring back all data rows but it does not.  Is there another way to get it to work?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

mlmccCommented:
The NZ needs to go on the database field.
0
marku24Author Commented:
The NZ in the database field with Like nz( [Forms]![frmHCMBP_ApprovalScreen]![cboCandidateFilter], "")&"*") worked great.  

This code:  ([CandidateIdentified] Is Null OR [CandidateIdentified] LIKE [Forms]![frmHCMBP_ApprovalScreen]![cboCandidateFilter])  - returned the 1 option from the combo box that I was selecting AND all the nulls when selecting from the combo box which is not what I was looking for.
0
mlmccCommented:
Crystal deserves some points.  Her comment will work.
1
marku24Author Commented:
sorry, I will adjust if I can.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
thanks for changing, Mark ~ you're welcome, happy to help (even if it didn't click right away)
... and thanks, Michael and South Mod ~
0
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.