Using Parameter Queries with Optional Parameters
Quote:
> I usually have been using Dynamic Queries... building an SQL string based
> on the users input, but am trying Parameter queries.
> I am having problems with paramaters that need to be optional. When
doing
> a dynamic query, the where clause could have conditions added to it, or
> just omitted. With the paramater queries, it seems you have to pass a
> value. Since there are about 5 optional parameters, and the user can
> select any combination of the 5, it is about impossible to have a
different
> query for each.
> Also, with the dynamic queries, I could add an order by clause. How can
I
> change what field the query is sorted on when using a parameter query?
Since there's no such thing as an optional parameter, you need to change
your thinking. Instead of making a selection criterion optional, you have
to change it so that it only takes effect if a parameter is supplied. So
you could put the following criterion in an Access QBE grid (the field
being checked is named strTestID, and the "optional" parameter is
strParameter):
=IIf(IsNull([strParameter]),[strTestID],[strParameter])
So if no parameter is set, this will always evaluate to True, otherwise
it'll only evaluate to True if the column equals the value of strParameter.
Similarly, you can add a column to your query like this:
strSortKey: IIf(IsNull([strParameter]),[strData],[strTestID])
and sort by it. This'll sort by strTestID if strParameter is provided, and
strData if not.
The downside of doing this is that it seems very unlikely that the Jet
engine is smart enough to use an index when it sorts a recordset on a field
like this. (I haven't tested it so I don't know for sure.)
I use parameter queries a lot, and once you understand them they're a great
time-saver. Sometimes it is just easier to build the SQL in code, though.
Hope this helps.
Bob Rossney
