Office 2010 - Access

Lesson 29: IF function and Wild Cards

29/76 Lessons 


We use “wildcards” in a query when we’re not sure of the spelling of text or numbers in a field.

When I have an asterisk character (*) after a number of letters that I typed into the “Criteria” field, “Access” will display all fields that begin with these letters, when I run the “Query”.


When I place an asterisk before and after a few letters in the “Criteria” field, “Access” will display all the fields in which these letters appear in that order.

Another “Wildcard” is the question mark (?).

The question mark replaces a single letter or digit. For example, ‘A?rts’ finds the words Aarts and Aerts. We use the two “Wildcard” characters together, in this example and when we run the query, “Access” will try to find all customers with this last name, regardless of the first name.


IF function

The IF function gives us two values, depending on the evaluation of the expression.

If the evaluations results in “true”, then it gives one value. If the evaluation was “false”, it gives a different value.

An example.

Depending on the result in “Query” below, I give the students the value “Pass” for each test when they score more than 55. If it is less, then I give them the value “Fail”.

Open the “Query” in “Design View”.
Right-click an empty square in the grid, and select “zoom in / out”.

This opens the “Zoom” dialog

The name that we are going to give this field is “Status”. I begin the expression with the word “Status”, followed by a colon. Then if you type IF function which is written as “IF”. The IF function consists of three parts, the expression, value if true and value if false. These three parts must be enclosed in parentheses and must be separated by a comma(,). The two values (fail / pass) should only be in quotes if they consist of text, not when they consist of numbers. The name of the field in which the expression has to rely must be enclosed in square brackets.

So if I follow all these rules, I have something like this:

Status: If ([score] <55, “failed”, “passed”)
if the “score” is less than 55 then he / she failed, if higher then he / she passed.
Click the OK button.

You can also type this directly in the field.

Click the “Run” button in the “Ribbon” when you’re done.


You've completed Lesson 29