Wildcard Search Query that Includes Null Values

Hello All,

I am currently working on the user front-end for a rather large MS Access database. I have several queries that search specific fields in the main table for a value selected from a drop-down box by the user. These fields are all allowed to, and do contain, Null values.

My question involves running a wildcard search. Currently the user may select "*" from the drop-down list of possible values to return any value in a given field. Unfortunately, as Access does not include Null values in the "*" set, this wildcard return-all search fails to return any record for which the searched field contains a Null value.

I have tried adding the Or Is Null statement to the criteria expressions in the relevant queries. While this solves the problem, it has the unacceptable side effect of always returning the Null values, even when a specific value and not the wildcard is entered by the user.

I need a way to allow the user to include the Null values when searching with the wildcard while simultaneously excluding Null values when searching for a specific term.

I have also tried to use an iif statement in the query criteria along the lines of:

Like IIf([FORMS]![myForm]![myControl]="*",Like "*" Or Is Null, [FORMS]![myForm]![myControl])

However, since Access does not handle the Null criteria properly when it is the result of an expression, this has not worked.

Ideally, Access would include a wildcard character that would include Null values. Unfortunately, I do not believe any such character exists.

Any suggestions/workarounds/solutions will be greatly appreciated. Thank you.

-Adroid
adroidAsked:
Who is Participating?
 
dqmqConnect With a Mentor Commented:
try this form:

where nz(YourField,"X") like "*"

 
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Try using a 2nd criteria line (OR condition) with IsNull

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
For Example:

SELECT tblEmp.EmpID, tblEmp.EmpName, tblEmp.TitleCD
FROM tblEmp
WHERE (((tblEmp.TitleCD) Like "A" & "*")) OR (((tblEmp.TitleCD) Is Null));

mx
0
 
GRayLConnect With a Mentor Commented:
WHERE Nz(FORMS]![myForm]![myControl],"") = Like "*"
0
 
adroidAuthor Commented:
Hello All,
Thanks for your help. I ended up using the Nz command (thanks for this suggestion, I was unaware of this command before) to replace the Null values with an arbitrary constant in a hidden calculated field in the query. I then ran the search on this field while displaying the corresponding original values in the visible field. This seems to work pretty well. Thanks again.

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

All Courses

From novice to tech pro — start learning today.