Multiple Parameters in a Query

[Guest]

I've searched the discussion group and I haven't been able to come across an
answer to my query problem. Here's the situation:

1. Select query as the basis for a report
2. in the query, I need three conditions to be met: a) PROGRAM equals a
particular program name, for example, "SouthEast Alliance"; b) STATUS =
"Active"; c)CLOSED DATE between a range to be specified by user
3. for the PROGRAM field, I have coded (for this example) the criteria =
"SouthEast Alliance"; the STATUS criteria I have = "Active"

Basically, I need the query to first select ALL records where PROGRAM =
"SouthEast Alliance" then from that group, select all Active records and
those CLOSED records between the user specified date range. The way I have
the query setup doesn't do what I need because it's reading Program AND
Status OR Closed Date so I end up with all active SouthEast Alliance clients
PLUS all of the closed clients from every other program!

Any suggestions on a way to design the query to do what I need? Thank you
for your time.

- Sharon

 

[Guest]

hi,
sometime people try to do too much with one query.
try queries within queries.
make a query that selects all of southeast alliance. then
use that query as source in a second query to select all
active data and so on. I have some queries that have 2,3,4
other queries in it as source.
i don't know your data structure so i'm limited to what i
can suggest but I've have used this technique in the past
successfully to narrow things down so to speak.
good luck.

 

[John Vinson]

I've searched the discussion group and I haven't been able to come across an
answer to my query problem. Here's the situation:

1. Select query as the basis for a report
2. in the query, I need three conditions to be met: a) PROGRAM equals a
particular program name, for example, "SouthEast Alliance"; b) STATUS =
"Active"; c)CLOSED DATE between a range to be specified by user
3. for the PROGRAM field, I have coded (for this example) the criteria =
"SouthEast Alliance"; the STATUS criteria I have = "Active"

Basically, I need the query to first select ALL records where PROGRAM =
"SouthEast Alliance" then from that group, select all Active records and
those CLOSED records between the user specified date range. The way I have
the query setup doesn't do what I need because it's reading Program AND
Status OR Closed Date so I end up with all active SouthEast Alliance clients
PLUS all of the closed clients from every other program!

Any suggestions on a way to design the query to do what I need? Thank you
for your time.

- Sharon

This should be pretty straightforward; it will probably be easier to
design in the SQL window than in the query grid, though, if you have
OR logic.

Just for starters, try putting either

"SouthEast Alliance"

or (maybe better)

[Enter program name:]

on the criteria line under Program; under Status put "Active", on the
same horizontal line (different lines use OR logic which you
apparently don't want); and on the same line again under CLOSED DATE
put

BETWEEN [Enter start date:] AND [Enter end date:]

If this doesn't work, please open the query in SQL view, copy and
paste it to a message here, and explain in what way it's failing.

John W. Vinson[MVP]