ignore criteria if null

[Achmed]

Hi everybody,

I'm trying to run a query to select data based on information entered in one of two fields in a form. For example, I want to select a list of people based on if the hair color or shoe size (these filds are not mandatory in the table). If I leave the hair color selection field in the form empty, the query should include people with all hair colors, and the same should work for shoe size. If I select a color, the list will show only people with that color and any shoe size. I hope this is clear.
My thoughts on this were to put something like the following criteria in the query:

If (HairColorEntered Is Null, show all people including those with null hair color, otherwise show those where HairColor = HairColorEntered).

How would I get Access to understand me? I feel so alone!
-Alan

 

[RickSpr]

Something like this should work:
WHERE IIf(IsNull(Forms![<formname>]![HairColor]), True, IIf(IsNull(HairColor), True, HairColor = Forms![<formname>]![HairColor]))

If the form [HairColor] field is Null, the first IIf returns True and the predicate becomes WHERE True. That's an odd looking predicate, but it's syntactically valid. Similarly, if the database HairColor field is Null, the second IIf returns True; only if neither is Null does the actual comparison determine the outcome. Rick Sprague

 

[Ricky1946]

Hello Achmed,
In the design view of the underlying query, enter the following in the criteria row for the particular field:

*********************************************************
Like [Forms]![FormName]![TextBoxName or ComboBoxName]& &quot;*&quot; Or [Forms]![FormName]![TextBoxName or ComboBoxName] Is Null
*********************************************************
This will pick up the value of that which is entered in the text/combo box but if nothing is entered it will accept a null value.
The query, when run and opened again will look strange as Access creates its own field at the end of the grid, but it works. I have done this on a number of occasions.
One word of warning - if you add another field to the search criteria later you may have to re-enter all of the search criterias as it seems to mess up the query workings.
Best of luck.
Ian s-) &quot;To say 'thankyou' encourages others.&quot;