Parameter in WHERE defaulting to All

[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".

 

[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

 

[MGFoster]

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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSjr3q4echKqOuFEgEQKnggCgu35bPKDSHivJBWX1TOtN1/NV744AoMDW
h6WUCx7Ox4/BFlA/8L4LvKmu
=uljl
-----END PGP SIGNATURE-----

 

[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

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

 

[MGFoster]

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;

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

MSQuery doesn't "like" Access parameter prompts. Actually, it doesn't
like any Access query unless it has only Standard SQL, or MSQuery's own
brand of SQL - which is very much like IBM's DB2 SQL.

MSQuery doesn't accept "[parameter] OR [parameter] IS NULL."

You'll probably have to run the queries from Access as an Automation
process. See the Access, or Excel, VBA Help articles "CreateObject
Function" and "GetObject Function" for more info. Or you can use Early
object binding (Dim xlObj As New Excel.Application) or Late object
binding (Dim xlObj AS Object). For that see the VBA Help article on
"New" for more info.

With Automation you can use Access queries "as is."

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSoy36YechKqOuFEgEQLE6gCfaNw7Q4HtqRg6J47h53mle/+QKdIAoP+R
qEtUIeQFRFdx4pxHTDPRdPcw
=GmnQ
-----END PGP SIGNATURE-----