Home and Learn: Microsoft Access Course
Access Criteria
There are lots and lots of different Criteria you can apply. To get you started, download this simple database table:
Prices Database (Right-click and Save As)
Open it up in Access. You'll see a single table called Prices. Double-click the Prices table in the All Access Objects area to get a look at the data. You'll see it's a list of fruit and veg, along with a very unrealistic price for each item. Let's play around with a query.
Click on the Create ribbon at the top of Access. Locate the Queries panel and select the Query Design Item. The Show Tables dialog box will appear. Select the Prices table and click Add:
Now close the dialog box.
In the area at the bottom, select Item for the first Field:
For the second Field, select Price:
Both columns will then look like this:
We'll now run a series of queries to return some prices.
Suppose we want to see which items were priced greater than 10.99. How would we do it? Well, there are some symbols called Conditional Operators. They are as follows:
| Operator | Meaning |
| > | Greater Than |
| < | Less Than |
| >= | Greater Than or Equal to |
| <= | Less Than or Equal to |
There are also a couple of Logic Operators you can use:
And
Or
Let's see how to use the various operators.
Click into the Criteria box for the Price. Enter the following:
> 10.99
This says, "Greater than 10.99". Your screen should look like this:
Now run your query (Click the red Run icon on the Design ribbon, in the Results panel.) You should see a list of prices returned:
This is a list of all the prices that are higher than 10.99.
To get back to your Query Design window, right click the tab, where it says Query1 in the image above. From the menu that appears, select Design View:
Now delete the current criteria and enter the following:
< 19.99
This says, "Less than 19.99". Your screen should look like this:
Run the query again to see the results:
Return to Design View, just like you did before (right-click the tab). Now try these queries:
>= 40
<= 20
The first one says, "Greater than or equal to 40". The second one says, "Less than or equal to 20". If you just use the > or the < then you won't get an exact match for your number. For example, if you entered > 40 as the criteria, it would bring back results where the prices are greater than 40. If a price is exactly 40 then it will be ignored. By typing >= 40 you are included all the prices that are 40 and above.
You can combine the conditional operators with the logic operators to construct more complex criteria. Try this one:
>= 20 And <= 40
This one has two conditions. The first is greater than or equal to 20. The second is less than or equal to 40. But we have the logic operator And in between the two. Now we're saying, "Return all the prices that are 20 and above AND 40 and less."
Run the query to see what results you get.
You can use Or, as well. Try this:
=3.2 Or 5.4
Run the query to see what happens.
Notice, though, that there is an Or box below the Criteria one. You
could enter all your "Or" conditions here:
We've entered = 3.2 in the Criteria box. Underneath it, we have three numbers in the Or boxes: 5.4, 4.75, 2.16.
Run the query and see what happens.
Now let's try some text criteria.
Text Criteria
You can use the word Like with some text in the criteria box. For example, suppose you wanted to find all items that started with a letter C. You'd do it like this:
Like "C*"
The asterisk is called a wildcard character. It means, "any character". So C* gets you anything that starts with the letter C. Try it out. Delete all the text from your Prices column. Enter Like C* in the Criteria box under Item: (Don't forget the double quotes.)
Run the query. The results are these:
Here are some more you can try:
Not Like "C*"
This one will get you all the items that don't begin with a letter C.
Another wildcard character is the question mark:
Like "??rn"
The question mark means any single character. In the query above, you're asking for any item with four characters where the last two are "rn". You can have as many question marks as you need:
Like "?ra???"
Note the double quotes. One set at the start and one at the end.
You can use square brackets for a range of characters. Suppose you want to search for all items that begin with the letters A, B or C. You'd do it like this:
Like "[A-C]*"
Again, note the double quotes at the start and end, after Like. In between a pair of square brackets, we have A-C. The hyphen means "to" (A to C). After the square brackets, we have an asterisk again.
One more to try. You can search for any item of a certain length. Try this:
Len([Item]) > "7"
Note where all the round and square brackets are. The Len() is something called a function. It is used to get the length of a string of text. In between the square brackets, you type a column name, Item in this case. We want anything in the Item column that is greater than 7 characters.
You can have more than one criteria. For example, you may want to search for all the items that begin with the letter C and have a price of greater than 70. In which case enter this for the Item Criteria:
Like "C*"
And this for the Price Criteria:
> 70
Your screen will then look like this:
Run the query and see what you get back.
OK, that's enough about Criteria. There's lot's more you can so with them, and we've only touched the surface. It's incredibly powerful way to search your database, though!
<-- Previous Lesson: Access Queries | Next lesson: Access Reports -->