Using wildcards in query criteria
Wildcarding is often used in queries when users are invited to enter
parameters (at run time). If they are being asked to enter a name they may wish
to have the option of entering a partial name to save time or because they may
not know the complete spelling. Alternatively they may want to see a larger
number of matching records which will result from entering just a few or
possibly no characters in the parameter.
| Table: Products |
ox |
| Name |
Code |
Reference |
| KYOCERA |
ABCDE |
1,634 |
| BROTHER |
AB3D |
456 |
| EPSON |
CBD45 |
13 |
| PACKARD |
|
741 |
| CANON |
AB1D |
45 |
| BROWN |
W |
2,155 |
When setting a query criteria, wildcards need to be identified by using the
keyword 'Like'. This can be followed a mixture of characters and wildcard
operators. The '*' can be used to denote any number of unspecified characters at
the specified position and '?' denotes a single unspecified character at the
specified position. The following examples make this clearer:
| Criteria |
Description |
Examples |
| Like "BRO*" |
All fields starting with BRO followed by any other
characters (or none) |
BROKEN or BROTHER |
| Like "AB?D" |
All fields starting with AB and finishing with D. Any single
character can appear in the 3rd position |
AB1D, ABBD but not ABCDE |
| Not Like "????" |
Any field which does NOT contain a four character string |
A or ABCDE |
| Like "1*" And >1500 |
All numbers starting with 1 and greater than 1500 |
1634 |
| Parameter Prompt |
ox |
| |
Enter a code (wildcards permitted) |
|
| |
AB?D |
|
| |
[OK] |
|
If users are prompted for parameter values they can either enter the wildcard
characters within their response or the wildcard characters can be added into
the query criteria setup.
| Parameter prompt |
Criteria |
Description |
| Enter product name (partial) |
Like [Enter product name (partial)] & "*" |
The user can enter any number of characters at the start of the name,
or none to return every record |
| Enter a code (wildcards permitted) |
Like [Enter a code (wildcards permitted)] |
The user is expected to enter a string and include any required *?
wildcard characters |
| Enter any part of the code |
Like "*" & [Enter any part of the code] &
"*" |
The user can enter a string of characters from any position in the
code, or none to return every record |
| Select products: Query design |
ox |
| |
|
| Field: |
Name |
Code |
Reference |
|
| Table: |
Products |
Products |
Products |
|
| Sort: |
|
|
|
|
| Show: |
Y |
Y |
Y |
|
| Criteria: |
|
Like "*" & [Enter any part of the code] &
"*" |
|
|
| Or: |
|
|
|
|
| |
