• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 59
  • Last Modified:

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] & "*"))
0
marku24
Asked:
marku24
  • 3
  • 3
  • 2
3 Solutions
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now