Criteria All Records

R

Robert Raley

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
 
R

Rick Brandt

Robert said:
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
Click to expand...

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.
 
R

Robert Raley

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 said:
Robert said:
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
Click to expand...

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.
Click to expand...
 
R

Rick Brandt

Robert said:
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;
Click to expand...

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?
 
D

Douglas J. Steele

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

(no e-mails, please!)



Robert Raley said:
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;
Click to expand...
 
Top