IIf in a Query

[Randey]

I am trying to get a query to return a record if an ID number is inserted in a form, otherwise to return all records if the form is blank. This is the code I am trying to use.

=IIf([Forms]![Form Filter]![Text20]="isnull","*",[Forms]![Form Filter]![Text20])

I don't get an error message; however, I get nothing but a blank query every time, whether I insert an ID number or not.

[zmbd]

because it does not appear that you are actually refering to any form in your equation.

Using parameters with queries and reports

[dgunner71]

Randy,

In your query, try adding a new value as such:

fxNewValue: IIf(nz([Forms]![Form Filter]![Text20],"")="", "*",[Forms]![Form Filter]![Text20])

Note that I've also swapped your IsNull statement for an Nz() function.

This will add a column in your query. The column will display an asterisk if there is no value in [Text20] or the value of [Text20] if there is.

Gunner

[zmbd]

dgunner71:
OP wants "otherwise to return all records if the form is blank. " as well; thus, this appears to be part of VBA code or the filter property of the form. In either case the OP-Equation still fails to refer to the form; however, because OP failed to provide enough detail as to how the equation was/is being used only a generic answer can really be provided.