Parameter in WHERE defaulting to All

D

Dorian

I need to do something like
WHERE Unit = [Enter Unit]
However, what I want is if no unit is entered, I want to return all Units.
I'd like to do this in a way that only results in one parameter prompt. Any
ideas? If it helps, Unit has only two possible values.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
J

John W. Vinson

I need to do something like
WHERE Unit = [Enter Unit]
However, what I want is if no unit is entered, I want to return all Units.
I'd like to do this in a way that only results in one parameter prompt. Any
ideas? If it helps, Unit has only two possible values.
-- Dorian

WHERE Unit = [Enter Unit] OR [Enter Unit] IS NULL
 
A

AFSSkier

John,

I tried what you suggested; WHERE Unit = [Enter Unit] OR [Enter Unit] IS
NULL.

It works great in MSAccess, but I get a syntax error in MSQuery. I need an
Excel parameter prompt where the user can get a list of ALL items or only the
KVI items.

The following is a test SQL that works in MSAccess, but not MSQuery for
Excel. The field kvi in the table has K or nulls.

SELECT DISTINCT a1.item_id, a1.kvi_id
FROM p_item AS a1
WHERE (a1.kvi_id Like [Enter "K" for KVI only]) OR ([Enter "K" for KVI only]
Is Null)
ORDER BY a1.kvi_id;

--
Thanks, Kevin


John W. Vinson said:
I need to do something like
WHERE Unit = [Enter Unit]
However, what I want is if no unit is entered, I want to return all Units.
I'd like to do this in a way that only results in one parameter prompt. Any
ideas? If it helps, Unit has only two possible values.
-- Dorian

WHERE Unit = [Enter Unit] OR [Enter Unit] IS NULL
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top