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.