Microsoft Access – Master IF Queries in 2 Minutes
Contents
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 at the end of this article for more details).
These are covered during our Microsoft Access training courses.
Another article you might find useful if you are not so familiar with Access is how to use Union Queries and Select Statements.
This article will help you get up to speed and enable you to start using Access properly.
Note that an IF statement in MS Access is classed as an Inline IF. As such 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.
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.
The IIF statement has to be set up 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. The expression can be written directly into the Field area:
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:
| TEST | Is there nothing (IsNull) in the Job No control of the Driver Run Sheet dialog box in our example. |
| TRUE | If there is nothing then a full criterion 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. |
| FALSE | Otherwise/else a full criterion of [Field] Operator Criteria is used: [JobNo]=to job number selected on the 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 criterion 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 Operators | Description |
| < | 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 Operators | Description |
| Is Null or Is Not Null | Determines 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. |