Wildcard Search Query that Includes Null Values

Posted on 2007-07-30
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

    Expert Comment

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

    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    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

    try this form:

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

    LVL 44

    Assisted Solution

    WHERE Nz(FORMS]![myForm]![myControl],"") = Like "*"

    Author Comment

    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

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    731 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now