Parameter Query (97)

Topic

New Reply

I have a cemetery database where the burial lots are organized by section (B thru R). I have created a parameter query to give me a list of lots by section (for a report). It uses the Between…And operators to ask the user for the first section and the last. My problem: I really want to display one of two conditions: one section, or all sections. I would like to use only one parameter. How do I select “all” with only one parameter?

Reply | Quote

Replies

There may be more elegant solutions than this, but here’s one that should work: Add “A” = [Enter Section] to your WHERE clause (with OR logic): e.g., something like ([Section] = [Enter Section]) OR (“A” = [Enter Section]). “A” needs to be unique (“All” would be another choice) — it can’t be one of your legitimate Section IDs. You can do this in Design mode or SQL mode. In design mode, specify “A” (with the quotes) as the field, uncheck the Show box, and specify [Enter Section] (without quotes) as a criteria (on its own row to indicate OR logic).

When you run the query and specify a legitimate section ID for the [Enter Section] parameter, the first subclause will pick up the lots belonging to that section. When you specify ‘A’ for the [Enter Section] parameter, the second subclause will pick up all the sections (that is, all the lots).

Tom

Reply | Quote

Tom,
Thanks for the post. The SQL suggestion worked fine. I tried to do the same thing in Design View but I couldn’t make it work, even though I know how to use OR logic on two lines. Did you want me to create a second column for Section?

Reply | Quote

Disregard previous post. I got it to work right in Design view.

Reply | Quote