Query Criteria, Part 1: Be specific!

(This post was first published in August 2011 and is the first in a series of posts about using query criteria, which includes Query Criteria Part 2: Like what? and Query Criteria Part 3: prompting for input by using a parameter.)

Queries are a fundamental part of developing and using any database. In addition to providing a way to make bulk changes to your data, they are how you ask questions about that data. Query criteria help you make your question more specific - so instead of asking "What are my contacts' birthdays?" you could ask "Whose birthdays are coming up next month?"

Let's look at these questions in Access. The first question is a very simple query of a single table (Contacts). It just asks Access to select the name and date of birth of everyone in the table:

When I run it, I get the full list:

Very basic stuff. In this particular example, I don't have a huge list to look at, but imagine if I had hundreds of contacts.  I probably wouldn't be interested in looking up all their birthdays very often. Now let's see how I could use a criterion to ask the more useful question: whose birthdays are this month?

Here's the query design again, only this time I've added an expression in the Criteria row of the DOB field:

Now when I run the query, I just see folks whose birthdays are this month:

You can read more about how query criteria work and get a comprehensive set of of examples in the article Examples of query criteria. If you liked this example but want to see something a bit more involved, check out this YouTube video about showing only those contacts whose birthday is today.

Next up in the series: using LIKE and wildcards in query criteria.

--Steven Thomas