[AccessD] It's About Time I Learned This

Rocky:

> Works really well, but it's not real convenient to work with - especially
> during development when the query needs tweaking.  I thought there was some
> way to inject the parameteres right into a stored query. Maybe not.
>

Susan Harkins and I wrote an article on a technique that does just that for
Access Advisor some time back.

In summary, you build a fixed query with a fixed where clause that ANDs all
of your form conditional parameters together, using expressions like:

((nz([Table].[Field]=[Forms]![MyForm]![cboField],True))=True)

The idea is when a value is selected in cboField, the expression returns
true only when Table.Field has a matching value. But, when cboField is null,
the inner comparison always evaluates to null, and the nz() always returns
true, in effect taking the comparison out of the where clause.

This might sound inefficient, but in practice it works very well. The more
complex the underlying query, the more benefit it gives.

This technique avoids:
* Constructing custom SQL in code with custom WHERE clauses, and all the
accompanying code
* The (sometimes considerable) delay that accompanies parsing complex SQL
for every new query.

-Ken