Home > Sample chapters > Microsoft Office > Access

Selecting Data Using Queries in Microsoft Access 2013

Filtering by single and multiple combinations of choices

Each row in the query grid allows you to specify a combination of choices, which when taken together will limit the data displayed. Each line on the query grid allows you to specify a separate set of choices. This means that a query can apply very sophisticated sets of criteria to limit the data returned.

Choosing to restrict the data displayed in each column bases the matching on ranges of values, sets of values, and patterns in the data.

Filter to match similar text

  1. In the criteria for a text field, type *al*. Access changes this to Like “*al*”, which will display any record containing the letters al as part of the data in this column.

Add a second set of criteria

  1. In the query grid, in the Or row, add further criteria by typing ba*. This displays all records where either the company name starts with the letters ba or the contact name includs the letters al.

The following table provides examples of using symbols in criteria to alter how data is matched.

Symbol

Example

Description

*

Like ‘*John’ or Like ‘John*’ or Like ‘*John*’

Wildcard searching in text fields. You do not need to type Like.

#

#01/10/2012#

Matches a date. You do not need to type the pound (#) symbol.

BETWEEN

BETWEEN 1 and 4

BETWEEN #01/10/2012# AND #05/10/2012#

Number or date range comparison. Includes the first and last criteria.

IN

IN(1,22,44,55)

IN(‘France’,‘USA’)

Set of data values.

<>, >, <, <=, >=,!=

> 25

<> ‘France’

General and arithmetic comparison; <> and != both mean not equal to.

[?]

Like ‘Fr[?]’ Would match Fra, FrB, frC ...

Character pattern matching. Match any character (numbers and letters). Different from * in that [?] will include only records with only one character after ‘Fr’. That is, it wouldn’t return ‘France’.

[#]

Like ‘000[#]’ Would match 0001, 0002...

Character pattern match (0–9) for a single character.

[A-Z]

Like ‘DNA[A-Z]’ Would match DNAA, dnaB...

Character pattern match (A–Z) for a single character.