[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Wildcard Search Query that Includes Null Values

Posted on 2007-07-30
Medium Priority
Last Modified: 2013-11-15
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.

Question by:adroid
LVL 75
ID: 19595557
Try using a 2nd criteria line (OR condition) with IsNull

LVL 75
ID: 19595561
For Example:

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

LVL 42

Accepted Solution

dqmq earned 750 total points
ID: 19595578
try this form:

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

LVL 44

Assisted Solution

GRayL earned 750 total points
ID: 19597015
WHERE Nz(FORMS]![myForm]![myControl],"") = Like "*"

Author Comment

ID: 19601712
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.


Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question