MeadInKent
| Feedback | Asking users for parameter values |
A selection of books about Access from Amazon

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:        
 


file: AccWildcard.htm Page last modified Mar14 MeadInKent.co.uk 2014