Refining Access Queries  «Prev 

Creating parameter queries

Advanced operators

Along with your standard comparison operators (<, >, =, <=, >=), you can also use the keywords Like and Between in your criteria. These operators give you some flexibility and convenience over the standard operators. Take a look.

Using the Like operator

The Like operator allows you to search based on partial fields, unlike the = operator, which allows you to search only based on the entire field.
For example, in the simulation for this lesson, you have to type the full name of the state to have records returned. This is not bad for using the State field, because it is only two letters. But what about if you wanted to use City?
The criteria row in the City field would have to be set to =[Enter City]. To take advantage of the Like operator, you would type Like [Enter City] & “*”. Note the use of the asterisk (*) at the end of the criteria. When the query is run again and you enter S, you will see all the cities that start with S, such as Seattle and San Francisco. You can see them here:

Like operator results
Like operator results

Using the Between operator

The Between operator allows you to look for criteria between (and including) two values. For example, if you want to find the records in the Hours table with DateWorked values between two dates, you would set the criteria row in the DateWorked column to Between [Enter Start] And [Enter End].
If the dates entered were 6/1/2009 and 6/15/2009, you would get those records whose DateWorked values fall between the two dates (including the two dates), as shown below.
Between operator results
Between operator results