Access and SQL Part 4: Building Queries "On the Fly"

Access and SQL Part 4: Building Queries "On the Fly"

This tutorial is about using SQL to build queries "on the fly" (or as we propellorheads say: "at run-time"). This means that you build the queries when you need them, rather than trying to anticipate the user's needs and preparing them in advance.
The tutorial will deal with regular "select" queries, ones which filter the source data and show the result to the user (as opposed to "action" queries which manipulate the data in some way).
You can download a copy of the database used in this tutorial. It contains completed examples of the forms, queries and code described in the tutorial. Follow the link at the bottom of this page. The database contains a table listing the details of the staff of a fictional multinational company. It contains the sort of personal details you might expect such as FirstName, LastName, BirthDate and Gender together with business details such as Office, Department, JobTitle and Email.
This tutorial is in two parts. This first part will show you how to create a fully working multi-purpose query. The second part will explain how to add some refinements to create a really professional query tool. You will find a link to the second part of the tutorial at the bottom of this page.
Why Build Queries on the Fly?
Like many Access developers, I tend to create very few stored queries. Instead I try to create a small number of general purpose queries that can be changed on demand to suit the user's requirements. This allows my databases to be much more flexible and I don't have to guess what the users might want to know. I can then use switchboards and dialog boxes to gather the information from the user which is used to create the query's SQL statement. The user is really building a query themselves but they don't need to know anything about Access to do it.
Another important reason for working this way is that inquisitive (or careless!) users might delete or change stored queries, and not know how to replace them.
Building a Multi-Purpose Query