6. MICROSOFT ACCESS QUERIES – ADVANCED
6.2.1: Like
Sometimes we need to get information based on partial information. Consider someone using the University database and wanting to find courses where the course description contains the word “computer”. To find courses matching this criterion we can use the Like operator where we specify an appropriate pattern. These patterns are defined using one or more wildcard characters. By default, our MS Access databases use the ANSI-89 standard for special wildcard characters.
Note: At some point you may want to investigate the more recent ANSI-92 standard for wildcards. You can change the standard your database is using by examining and changing the MS Access Options for Object Designers/Query Design.
The ANSI-89 wildcard characters are:
|
Wildcard Character |
Matching criteria |
Example |
|
* |
Matches any number of characters |
Like “1*” matches all text strings that start with “1” |
|
? |
Matches any single character |
Like “a?c” matches “aac”, “abc”, “acc”, etc. but does not match longer strings such as “aacc” or “xabc” |
|
# |
Matches any single numeric character |
Like “b#b” would match “b2b” and “b7b” but not “bam” |
|
[] |
Matches any single character within the brackets |
Like “j[ai]m” matches “jim” and “jam” but not “jaim” |
|
! |
Used with [] when you do not want to match any of the enclosed characters |
Like “b[!ao]b” matches “bim” and “bub” but not “bam” or “bob” |
|
– |
Used with [] to specify a range of matching characters (given in ascending sequence) |
Like “b[0-9]b” would match to “b2b” but not to “bam” Like “b[a-c]b” would match “bab”, “bbb”, and “bcb” |
Figure 6.4: ANSI-89 wildcard characters
Example
To list courses where the description begins with “This course” you need a pattern where you specify that a text value begins with “This course” which can be followed by anything else: “This course*”.
And so, in QBE you enter the criteria for title: Like “This course*”:
