Simple one - Syntax problem

[Raymond & Lisa Yucha]

I have a form with 3 controls (combo boxes). Each one of these controls is
passed back to a query based on what the user selects. The user may select
values from 1, 2 or all 3 controls (one control will have to be selected).
In my query design under criteria, if I simply place the referenced control
in the appropriate column Access looks for values from all 3 controls. This
becomes a problem when the user does not select all three controls. I know
I can use an if statement in the criteria of the query. So far this is what
I have. (All 3 criterias in the query would look similar to this).

iif([forms]![formname]![control1] is null, *,
[forms]![formname]![control1] )

This would take the control value for the criteria if there is one or return
all records if no control is selected. My problem is Access is choking on
the true scenario (the * case). Can someone help me with the syntax on how
to get Access to recognize the *. Thanks in advance.

Ray

 

[Wayne Morgan]

iif([forms]![formname]![control1] is null, Like "*",
[forms]![formname]![control1] )

This won't return Null values in the field.

 

[Raymond & Lisa Yucha]

Thanks guys. I had tried Wayne's method (and a bunch more) before I posted
and it never worked. Thanks Doug for straightening things out. This is
very useful. I wish I had asked someone earleir, I could have made my apps
better.

Ray

Sorry, Wayne, that won't work. You can't change the operator in the IIf
statement like that (plus you need to use the IsNull function, not Is Null)

Try:

Like IIf(IsNull([forms]![formname]![control1]), "*",
[forms]![formname]![control1])


--
Doug Steele, Microsoft Access MVP

iif([forms]![formname]![control1] is null, Like "*",
[forms]![formname]![control1] )

This won't return Null values in the field.

--
Wayne Morgan
Microsoft Access MVP

I have a form with 3 controls (combo boxes). Each one of these

controls

is

is

what

I have. (All 3 criterias in the query would look similar to this).

iif([forms]![formname]![control1] is null, *,
[forms]![formname]![control1] )

This would take the control value for the criteria if there is one or return
all records if no control is selected. My problem is Access is

choking

on

on

how