Kenneth Ismert
kismert at gmail.com
Fri Oct 9 13:52:05 CDT 2009
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