sql - How do you create a parameterized query in MS Access 2003 and use other queries/forms to fill the parameters and obtain a resultset

Question

I'd like to be able to create a parameterized query in MS Access 2003 and feed the values of certain form elements to that query and then get the corresponding resultset back and do some basic calculations with them. I'm coming up short in figuring out how to get the parameters of the query to be populated by the form elements. If I have to use VBA, that's fine.

Answer 1

References to the controls on the form can be used directly in Access queries, though it's important to define them as parameters (otherwise, results in recent versions of Access can be unpredictable where they were once reliable).

For instance, if you want to filter a query by the LastName control on MyForm, you'd use this as your criteria:

LastName = Forms!MyForm!LastName

Then you'd define the form reference as a parameter. The resulting SQL might look something like this:

PARAMETERS [[Forms]!MyForm![LastName]] Text ( 255 );
SELECT tblCustomers.*
FROM tblCustomers
WHERE tblCustomers.LastName=[Forms]![MyForm]![LastName];

I would, however, ask why you need to have a saved query for this purpose. What are you doing with the results? Displaying them in a form or report? If so, you can do this in the Recordsource of the form/report and leave your saved query untouched by the parameters, so it can be used in other contexts without popping up the prompts to fill out the parameters.

On the other hand, if you're doing something in code, just write the SQL on the fly and use the literal value of the form control for constructing your WHERE clause.