Passing vars to a query

[Dennis]

Okay, despite the simplicity suggested by the thread title, this one is a bit
unusual. In Access2002, I have a query set up. (So far, simple, right?)
Anyway, I have a form that I want the user to select one-to-may assorted
criteria, such as a date range, a building location, a person's name, a
vehicle license plate number, etc. Again, the user can select as few or as
many as he wants.

Now, at this point, I've defined several PUBLIC functions that are used to
pass these values into the query. Also easy enough. BUT.... what if the user
leaves some textbox controls empty (isn't using them for the record lookup.)
How can I tell the query to use that function's value UNLESS it's blank, in
which case ignore that one and select "all" for that column? I have no idea
how to make that happen.

Now, I can build my own SQL string for the combined query criteria, and I
know I can create pass-through queries using that SQL string, so might this
be the way to go? If so, what VBA commands/syntax would I use to create a new
query, fill it with the SQL information, save it, execute it, and delete it
when done? Also, the report would have to know to use that query as its
record source. Can that even be done (in advance) in the report, when I'd be
creating it on-the-fly?

Any suggestions would be most helpful. Again, this is for a report, not to
pull up records in a form. Access2002.

thanks for any ideas you might come up with!

 

[Jeff Boyce]

Dennis

Rather than trying to customize a query to handle any/all possible
combination of selection criteria from your form, consider doing this
entirely IN your form.

If you dynamically build a SQL statement in code-behind-form to handle any
number of selection criteria, then you can open the report, passing it the
SQL string as a WHERE clause, using a command button on the form.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dennis]

AHA! I see that now! I never noticed that before. Okay, so what do I use as
the "Control Source" for the report itself? Just blank it out?

 

[Jeff Boyce]

Dennis

Create a "base" query that would return ALL records as the source for the
report. Then, when you open the report with the WHERE clause, the base
query is, in effect, customized.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dennis]

Killer! I already have that, so all I have to do is build that SQL string
(also about 80% complete already) and pass it to the report on the Open
command.

Thanks a million!