Query Criteria

  • Thread starter Thread starter Big Tony
  • Start date Start date
B

Big Tony

My query criteria refers to a named textbox in a form
that specifies the criterion for the query. My question
is the following. How can I write an IIF statement so
that the query will not specify any criteria if I leave
the named textbox blank?
 
My query criteria refers to a named textbox in a form
that specifies the criterion for the query. My question
is the following. How can I write an IIF statement so
that the query will not specify any criteria if I leave
the named textbox blank?
Click to expand...

Do you wish all records returned if the text box is empty?

Like IIf(IsNull(forms!FormName!ControlName),"*",
forms!FormName!ControlName)
 
Field: YourField
Criteria: Forms!YourForm!YourTextbox or Forms!YourForm!YourTextbox Is Null

Access will reformat that if you are doing this in the query grid and if you
have multiple criteria in the query, you may end up with a query to complex error.

You can't really use an IIF statement here, unless your field ALWAYS has data.
Then you can get away with

Criteria: IIF(Forms!YourForm!YourTextbox is Null,YourField,Forms!YourForm!YourTextbox)

Or if it is a text field this would probably be much faster, since the criteria
line would only have to be evaluated one time.
Criteria: LIKE IIF(Forms!YourForm!YourTextbox is Null,"*",Forms!YourForm!YourTextbox)

UNTESTED thought (still would fail on Nulls)

Criteria: NZ(Forms!YourForm!YourTextbox,YourField)
 
Back
Top