Weekly Computer Tip # 125
3 September 2005

using wildcards in parameter queries (Microsoft Access)

Hope you all had a great Bank Holiday Weekend. For our non-British subscribers ... A Bank Holiday is a public holiday and we had one last Monday. Although apparently there is no legal right to time off on these days, the majority of the population receive them as holidays and I decided to give myself the weekend off too. Hence no tip; with thanks to those of you who queried it! Speaking of queries ...

A few weeks ago I explained you don't have to be a psychic and anticipate the various combinations of query criteria that you are going to need in Access. We saw that, for example, if you often want to retrieve all clients in a particular city, you can create a so-called parameter query, which will prompt you for information when the query is run. That way you can make changes to the criteria without having to change the query design grid every time you run the query.

Well, you can even use wildcards in these parameter queries, but I feel they're not very intuitive to create.

Here's how:

1. Create a query as normal.
2. With the Query window displayed in Design view, click in the Criteria row for each field you want to use as a parameter.
3. For a single wildcard enter
   Like [type prompt here] & "*" OR
   for two wildcards enter
   Like "*" & [type prompt here] & "*"
4. View the results by clicking on the View or Run button on the toolbar.

For instance, the parameter query critera

Like [Find city starting with] &"*"

creates a prompt in which you can enter the first letter or string of letters of the city you want to see.

Whereas Like &"*" [Which city?] &"*"

creates a prompt in which you can enter any letter or string of letters that occurs anywhere in the city field.

Finally, we still have a few places left for the Mastering Microsoft Access seminar, which is to be run on Thursday, 29 September at the Fitzwilliam Conference Centre in Cambridge. The cost for weekly tip subscribers is just GBP225. And remember, because every business (and hence every database!) is different, you get my own individual support to help you apply what you learn during the seminar to your own business. What's more, I'm giving anyone who enrols before this time next week (Saturday 10 September) a free Microsoft FrontPage 2000 Introduction manual, suitable for self-study. For full seminar details and booking information see http://www.roem.co.uk/handsoff_access.html

Until next week.

PS I’m committed to sharing the best of what I know with others so please don’t keep me a secret. If you enjoyed today’s tip, please forward it to anyone you feel may benefit. Alternatively, feel free to reprint it (with full copyright and subscription information) in your newsletters and message boards.

Home   Training   Support   Clients   Contact Details   News   Disclaimer