A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Criteria All Records



 
 
Thread Tools Display Modes
  #1  
Old August 28th, 2005, 04:02 PM
Robert Raley
external usenet poster
 
Posts: n/a
Default 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  
Old August 28th, 2005, 04:17 PM
Rick Brandt
external usenet poster
 
Posts: n/a
Default

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  
Old August 28th, 2005, 05:09 PM
Robert Raley
external usenet poster
 
Posts: n/a
Default

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  
Old August 28th, 2005, 05:23 PM
Rick Brandt
external usenet poster
 
Posts: n/a
Default

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  
Old August 28th, 2005, 05:32 PM
Douglas J. Steele
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 11:49 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.