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
|
|||
|
|||
Criteria All Records
Access 2003
I am using a query to return a group of products. in the criteria box I have this [Please enter product number] Obvisouly when the query is run the user is prompted to input the product number. This works great. What should the user input to return all products or what is the code to write for the criteria. Thanks Bob Raley |
#2
|
|||
|
|||
Robert Raley wrote:
Access 2003 I am using a query to return a group of products. in the criteria box I have this [Please enter product number] Obvisouly when the query is run the user is prompted to input the product number. This works great. What should the user input to return all products or what is the code to write for the criteria. Thanks Bob Raley For your criteria use... [Please enter product number] or [Please enter product number] Is Null If the user enters nothing in the prompt you should get all records. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#3
|
|||
|
|||
Hi Rick you suggestion worked great with the query that I was using, thanks.
I applied the same principle to a union query that I am using and now matter what I input the return is all. Please take a look and tell me what I am doing wrong. Thanks Bob SELECT ContId, com_name, studio FROM qry_mod_min_F4F; UNION SELECT ContId, com_name, studio FROM qry_mod_min_PS; UNION SELECT ContId, com_name, studio FROM qry_mod_min_DS; UNION SELECT ContId, com_name, studio FROM qry_mod_details; UNION SELECT ContId, name, studio FROM qry_mod_insurance; UNION SELECT ContId, com_name, studio FROM qry_mod_manager; UNION SELECT ContId, com_name, studio FROM qry_mod_shift WHERE (((studio)=[Please enter studio number] Or [Please enter studio number] Is Null)) ORDER BY studio, com_name; "Rick Brandt" wrote in message ... Robert Raley wrote: Access 2003 I am using a query to return a group of products. in the criteria box I have this [Please enter product number] Obvisouly when the query is run the user is prompted to input the product number. This works great. What should the user input to return all products or what is the code to write for the criteria. Thanks Bob Raley For your criteria use... [Please enter product number] or [Please enter product number] Is Null If the user enters nothing in the prompt you should get all records. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#4
|
|||
|
|||
Robert Raley wrote:
Hi Rick you suggestion worked great with the query that I was using, thanks. I applied the same principle to a union query that I am using and now matter what I input the return is all. Please take a look and tell me what I am doing wrong. Thanks Bob SELECT ContId, com_name, studio FROM qry_mod_min_F4F; UNION SELECT ContId, com_name, studio FROM qry_mod_min_PS; UNION SELECT ContId, com_name, studio FROM qry_mod_min_DS; UNION SELECT ContId, com_name, studio FROM qry_mod_details; UNION SELECT ContId, name, studio FROM qry_mod_insurance; UNION SELECT ContId, com_name, studio FROM qry_mod_manager; UNION SELECT ContId, com_name, studio FROM qry_mod_shift WHERE (((studio)=[Please enter studio number] Or [Please enter studio number] Is Null)) ORDER BY studio, com_name; Each SELECT statement in the UNION query has to have its own WHERE clause. As written the WHERE clause will only apply to the last SELECT. Was that your intent? -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#5
|
|||
|
|||
Couple of things.
The intermediary semi-colons are wrong. Semi-colons at the end of a SQL statement is actually optional in Access, but it's definitely not required in the middle of the statement. Also, the WHERE clause you've defined is only going to be applied to the last SELECT statement, not all of them UNIONed together. You could save your UNION query without the WHERE clause, then create a second query that queries the UNION query, or you could use SELECT ContId, com_name, studio FROM qry_mod_min_F4F WHERE (((studio)=[Please enter studio number] Or [Please enter studio number] Is Null)) UNION SELECT ContId, com_name, studio FROM qry_mod_min_PS WHERE (((studio)=[Please enter studio number] Or [Please enter studio number] Is Null)) UNION SELECT ContId, com_name, studio FROM qry_mod_min_DS WHERE (((studio)=[Please enter studio number] Or [Please enter studio number] Is Null)) UNION SELECT ContId, com_name, studio FROM qry_mod_details WHERE (((studio)=[Please enter studio number] Or [Please enter studio number] Is Null)) UNION SELECT ContId, name, studio FROM qry_mod_insurance WHERE (((studio)=[Please enter studio number] Or [Please enter studio number] Is Null)) UNION SELECT ContId, com_name, studio FROM qry_mod_manager WHERE (((studio)=[Please enter studio number] Or [Please enter studio number] Is Null)) UNION SELECT ContId, com_name, studio FROM qry_mod_shift WHERE (((studio)=[Please enter studio number] Or [Please enter studio number] Is Null)) ORDER BY studio, com_name; or (assuming Access 2000 or higher) you can use SELECT ContId, com_name, studio FROM ( SELECT ContId, com_name, studio FROM qry_mod_min_F4F WHERE (((studio)=[Please enter studio number] Or [Please enter studio number] Is Null)) UNION SELECT ContId, com_name, studio FROM qry_mod_min_PS WHERE (((studio)=[Please enter studio number] Or [Please enter studio number] Is Null)) UNION SELECT ContId, com_name, studio FROM qry_mod_min_DS WHERE (((studio)=[Please enter studio number] Or [Please enter studio number] Is Null)) UNION SELECT ContId, com_name, studio FROM qry_mod_details WHERE (((studio)=[Please enter studio number] Or [Please enter studio number] Is Null)) UNION SELECT ContId, name, studio FROM qry_mod_insurance WHERE (((studio)=[Please enter studio number] Or [Please enter studio number] Is Null)) UNION SELECT ContId, com_name, studio FROM qry_mod_manager WHERE (((studio)=[Please enter studio number] Or [Please enter studio number] Is Null)) UNION SELECT ContId, com_name, studio FROM qry_mod_shift ) As UnionQuery WHERE (((studio)=[Please enter studio number] Or [Please enter studio number] Is Null)) ORDER BY studio, com_name; -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Robert Raley" wrote in message ... Hi Rick you suggestion worked great with the query that I was using, thanks. I applied the same principle to a union query that I am using and now matter what I input the return is all. Please take a look and tell me what I am doing wrong. Thanks Bob SELECT ContId, com_name, studio FROM qry_mod_min_F4F; UNION SELECT ContId, com_name, studio FROM qry_mod_min_PS; UNION SELECT ContId, com_name, studio FROM qry_mod_min_DS; UNION SELECT ContId, com_name, studio FROM qry_mod_details; UNION SELECT ContId, name, studio FROM qry_mod_insurance; UNION SELECT ContId, com_name, studio FROM qry_mod_manager; UNION SELECT ContId, com_name, studio FROM qry_mod_shift WHERE (((studio)=[Please enter studio number] Or [Please enter studio number] Is Null)) ORDER BY studio, com_name; |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
querie Criteria date range and find records on ProductType | Rodrigo R via AccessMonster.com | Running & Setting Up Queries | 1 | August 5th, 2005 11:26 PM |
REQ: Need Criteria expression that will ask for "from" and "to" dates and then select records that fall within that date span for access 2000 Please...TIA | Ralph Malph | General Discussion | 8 | April 19th, 2005 07:36 AM |
REQ: Need Criteria expression that will ask for "from" and "to" dates and then select records that fall within that date span for access 2000 Please...TIA | Ralph Malph | Running & Setting Up Queries | 2 | April 18th, 2005 05:21 AM |
In Access, How can I set criteria to only choose records NOT cont. | schwdm | Running & Setting Up Queries | 1 | January 27th, 2005 06:15 PM |
Complex query criteria - desperate appeal | Ted Allen | Running & Setting Up Queries | 5 | November 17th, 2004 07:14 PM |