IIf in query criteria

[LMcMillan]

Hi,
I've been trying to get IIf statements to work in Query criteria. It doesn't seem to matter how I phrase it, IIf statements just don't seem to be accepted, no records are ever returned.
Has anyone got an example of an IIf statement they actually got to work?
Thanks.

 

[Rich]

Iif statements are used not as criteria but in the query grid , criteria would be >1 or =Forms!SomeForm!ControlName, or ="A" etc.
Post back with some details of what you are trying to do.

 

[Fornatian]

Rich,

I know it's not elegant but you can put Iif statements in the criteria row suchas:

IIf(IsNull([Forms]![Form1]![Text0]),"bdecw18","bdecw43")

You can't put If statements into the query grid.

Ian

 

[LMcMillan]

Basically I'm performing a query which reads in data which the user has inputed into a form and then performs a query based on that data.

The user could say:
Type in 'M*' into a lastname field on the form, and the query would then bring up all records who have a lastname beginning with 'M'
Alternatively the user could use just * to signify he/she wants all values for that field.

Unfotunately, not all the records have values in all fields. That is, some records contain null values in some fields.
The problem is that the wildcard * does not include null values.

I was hoping to use an IIf statement to test if a field was null or had a value.

Hmmm, that got a bit longwinded. Hope you can understand what's going on....
Thanks,
Lindsay

 

[Fornatian]

On one row of the query criteria put:

Like "*" & Nz([Parameter Text],"") & "*"