IIF with null included field

[samhu]

Hi All,

I am currently working on an instrument datebase, I have a mainquery that takes care of user inputs from a form. The main fields that have been queried on are Type, System, and Manufacturer and they are all look-up fields that contain some null values.

On the same criteria row for these fields, I have

Like IIf([forms]![User Interface].[qtype2]="","*",[forms]![User Interface].[qtype2])

Like IIf([forms]![User Interface].[qsys2]="","*",[forms]![User Interface].[qsys2])

Like IIf([forms]![User Interface].[qman1]="","*",[forms]![User Interface].[qman1])

qtype, qsys and qman are the user inputs from the user interface that returns look-up table values.

This works fine when all 3 of these fields are all filled out for a certain instrument. The problem arise when some fields of the instrument are left blank or is null. The instrument won't show up in a query at all. What I wanted it to do is to show everything including the ones with null fields when the user input are null or "". When the user specifies certain requirement I only want to show the ones that are not null. I understand that putting them on the same row means AND, I have tried to OR them and did not have the result i wanted.

Please let me know how to fix this issue, or if you have a better implemtation for this sort of query.

Thanks in advance guys!

 

[Brianwarnock]

Like "*" does not select null fields.

You need to remove the criteria you have and in SQL view enter

Where (Type= [forms]![User Interface].[qtype2]) or [forms]![User Interface].[qtype2] is null) and
(System = etc


Having done this always save the query from SQL view as the design view Ui makes this. Simple code very complex.

This works by selecting all cases where the CRITERIA. Is null but only the records that match if it is not.

Brian

 

[samhu]

Hey Brian,

Much thanks to your quick and helpful response, everything works like a charm when I am in SQL mode. I have to agree that the Deisign view had turned this simple logic into a beast, but the auto compelte feature is always great to have, I wonder if they have that in the SQL mode.

Anyways.

Here is some detail on how I fixed my issue.
Since my default value for the UI I made is "", I had to change brian's code a bit to fit into my use.

In the where clause,
WHERE ([forms]![User Interface].[qtype2] = "" OR [Instrument ID].[Instrument Type] = [forms]![User Interface].[qtype2] ) AND ([forms]![User Interface].[qsys2] = "" OR [Instrument ID].[System] = [forms]![User Interface].[qsys2]);

Hope this might help others if they ever run into this type of problem.

-Sam

 

[Brianwarnock]

Sorry for giving you the standard Is Null approach should have tailored it to ="". , the upside is that you proved that you understood what was going on.

In fact some users like to have "ALL" in their combos in which case it would have ="ALL" as the test.

Pleased to have helped

Brian