Microsoft Access – Using IF Queries as Search Criteria

IF Statements as Query Criteria

This article assumes a knowledge of the standard use of the Criteria row in a Microsoft Access Query and the Operators, for example =, <>, Like, IS that can commonly be used there (see Appendix – Operators).

Note that an IF statement in MS Access is classed as an Inline IF. Assuch the correct syntax is actually IIF.

When do you need IF in a Criteria?

A common requirement for an IIF statement in a query’s criteria is where multiple selection criteria have been delivered as a collection of controls/”choices” on one dialog box.

 

MS Access - IIF Statement Dialogue Box

For example this dialog box allows the operator to find data based on a wide combination of choices but not all choices will be used at any one time.

It is essential that those criteria/controls/choices not used do not in any way impact on the data that is returned.

To this end, a statement similar to IIF a Job No is not specified select all Job Numbers so that the first criteria picked up in this example is then the Date range, but if a Job No is provided that should be the only record selected.

However, an IIF statement cannot be used in the Criteria area of an Access Query as an Operator is required, for example: =,>=, <>, Like, Between… (see Appendix – Operators) for further examples.

In the absence of such an operator Access automatically, although not visibly, adds = to the front of the statement changing the way it is read to essentially “Field = IIF Statement” which makes no sense as a Field cannot equal an IF statement that is returning a Criteria.

The result of such a query would be no records returned.

The Solution

Using the above example one might naturally think the following would be correct but it is not for the reasons already explained above.

MS Access - Incorrect Query Setup Example

The IIF statement has to be setup as a Field in the query. This is where calculations and formulae are normally placed, however, in this case no field name has to be given. Theexpression can be written directly into the Field area:

 

MS Access - 3 - Correct IIF Query Setup Example

IIf(IsNull([Forms]![dlgDriverRunSheets]![txtJobNo]),[JobNo] Like “*”,[JobNo]=[Forms]![dlgDriverRunSheets]![txtJobNo])

The above IIF statement when broken down provides a “What to do if it’s TRUE” and a “What to do if it’s FALSE” section. It gives full stand-alone criteria for the [Field] Operator Criteria in its own right:

TESTIs there nothing (IsNull) in the Job No control of the Driver Run Sheet dialog box in our example.
TRUEIf there is nothing then a full criteria of [Field] Operator Criteria is provided. [JobNo] Like “anything” using the wildcard “*” which will pick up ALL Job Numbers thereby ensuring the absence of a Job Number has no impact on what records are selected.
FALSEOtherwise/else a full criteria of [Field] Operator Criteria is used: [JobNo]=to job number selected on dialog box. Only records with the same number as the selected Job No will be selected.

In this example where no Job No is selected via the dialog box the IIF statement returns True which sets a criteria to select all job numbers. Where a Job No is provided on the dialog box, valid or otherwise, the result of the IIF statement becomes False which sets the criteria to select only matching job numbers.

For records in the data set being queried which meet whichever of the two criteria has been selected by the action of the IIF statement, a value of -1 is returned. For those records that do not match the selected criteria a 0 which equates to False.

To select only those records that match the selected criteria enter <>False in the Criteria row of the Query.

Note that in some database products True is returned as positive 1 and not negative and in some products any value other than 0 is also True, but False is always 0 and for this reason it is safer to use a criteria of <>False rather than True.

As the column in the query is not required visually and is only acting as a filter/criteria untick the tick box in the row marked Show.

In summary the column in the query with the IIF statement would read as follows:
Field: IIf(IsNull([Forms]![dlgNameOfForm]![ControlOnForm]),[Field] Like”*”,[Field]=[Forms]![ dlgNameOfForm]![ ControlOnForm])
Show: Uncheck
Criteria: <>False

Appendix – Operators

Access criteria require an Operator to appear first. Listed below are standard comparison operators and also the more special criteria specific operators.

For both Comparison and Special Operators a result of True or False is returned as described in the table and when applied as Criteria in an Access Query when True is returned for a record the corresponding record is selected.

Comparison Operators
 

Comparison OperatorsDescription
<Returns True if the first value is less than the second value.
<=Returns True if the first value is less than or equal to the second value. > Returns True if the first value is greater than the second value.
>=Returns True if the first value is greater than or equal to the second value.
=Returns True if the first value is equal to the second value.
<>Returns True if the first value is not equal to the second value. It is also referred to as NOT as this article uses <>False

Special Criteria related operators

Special OperatorsDescription
Is Null or Is Not NullDetermines whether a value is Null or Not Null, where Null is empty.
Note that a space in a field is not empty despite not being visible.
Like “pattern”Matches string/text values by using the wildcard operators such as ? for an unknown single character or * for any number of unknown characters.

This article uses Like “*” in the IIF statement to return all records where no Job No is specified.

Between # And #Determines whether a numeric or date value is found within a range.
In(val1,val2…)Determines whether a value is found within a set of values.

See here for more details of our MS Access training courses.

Share on LinkedInShare on FacebookTweet about this on TwitterShare on Google+Email this to someone