If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple Parameters in a Query
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 |
#2
|
|||
|
|||
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. -----Original Message----- 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 . |
#3
|
|||
|
|||
On Fri, 29 Apr 2005 09:36:08 -0700, "SLEJ"
wrote: 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] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Query to contain data from 2 queries but get multiple entries | KIM | Running & Setting Up Queries | 2 | March 14th, 2005 09:25 AM |
Date query parameters entered from form not working | kraymond | Running & Setting Up Queries | 1 | February 21st, 2005 05:41 PM |
passing parameters from a multiple listbox selection to a query | Lorenzo | General Discussion | 1 | February 4th, 2005 09:09 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Taher | Setting Up & Running Reports | 1 | August 31st, 2004 09:07 PM |