Reference the controls on the form as the parameters. Lets take a simple
example of a Products and Suppliers databse where you want to restrict a
query by the City in which a supplier is located and/or the Product selected
from combo boxes on a form. The query might go like this:
SELECT Product, Supplier, City
FROM Products, ProductSuppliers, Suppliers
WHERE Products.ProductID = ProductSuppliers.ProductID
AND ProductSuppliers.SupplierID = Suppliers.SupplierID
AND (City = Forms!YourForm!cboCity
OR Forms!YourForm!cboCity IS NULL)
AND (Product = Forms!YourForm!cboProduct
OR Forms!YourForm! cboProduct IS NULL);
By testing the parameters for NULL in the parenthesised Boolean OR
operations this makes them optional, i.e. a user can select a City or
Product, both or neither and have the relevant rows returned. In the click
event procedure of a button on the form you can open the query:
DoCmd.OpenQuery "YourQuery"
or better still open a bound form or report whose RecordSource is the query,
e.g.
DoCmd.OpenForm "YourForm"
or:
DoCmd.OpenReport "YourReport", View:=acViewPreview
One thing I'd add; if the parameters take date values its important to
declare them as DateTime in the query, otherwise Access could interpret a
date parameter in short date format as an arithmetical expression and give
the wrong results, e.g. if defining a date range:
PARAMETERS
Forms!YourForm!txtStartDate DATETIME,
Forms!YourForm!txtEndDate DATETIME;
SELECT *
FROM YourTable
WHERE YourDate >= Forms!YourForm!txtStartDate
AND YourDate < Forms!YourForm!txtEndDate + 1;
In this example note how the date range is defined. This is more reliable
than a BETWEEN….AND operation as it takes account of date/time values which
might include non-zero times of day (possibly without you realizing it). A
BETWEEN….AND operation would not return any rows with such values on the last
day of the range.
Ken Sheridan
Stafford, England
