Parameter Query

[Tim]

Hi folk,

I have the following query in my db:

SELECT Sales.Invoice, Sales.Code, Sales.IDate
FROM Sales
WHERE (((Sales.IDate)=[forms]![form1]![text0]));

It works fine but I also want the query return all
records when user put "all" into the [forms]![form1]!
[text0]. Could anyone show me how to do it?

I tried something like this:

SELECT Sales.Invoice, Sales.Code, Sales.IDate
FROM Sales
WHERE (((Sales.IDate)=IIf([forms]![form1]![text0]="all",
(Sales.IDate) Like "*",[forms]![form1]![text0])));

But it didn't work.

Please help.

Thanks in advance.

Tim.

 

[MGFoster]

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

Instead of having the word "All" in the TextBox - leave it blank
(NULL) and use a WHERE clause like this:

PARAMETERS [forms]![form1]![text0] DateTime;
....
WHERE [forms]![form1]![text0] Is Null
OR (IDate = [forms]![form1]![text0]
AND [forms]![form1]![text0] Is Not Null)

I got this solution from an MS example db of queries:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;182568

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQC2r6oechKqOuFEgEQL92QCfbOGXRNyB47SU3d2kDpFCaWG8c5oAoPaY
9jS05bJY4Mq84zFQfVXi9fRD
=tb8b
-----END PGP SIGNATURE-----

 

[John Spencer (MVP)]

One way (as long as IDate does NOT contain nulls), that could work.

WHERE Sales.IDate =
IIF(Forms!Form1!Text0 = "All",
Sales.IDate, CDate(Forms!Form1!Text0))

 

[Tim]

John and MGFoster,

Thanks for your help. They work great.

Tim.

-----Original Message-----
One way (as long as IDate does NOT contain nulls), that could work.

WHERE Sales.IDate =
IIF(Forms!Form1!Text0 = "All",
Sales.IDate, CDate(Forms!Form1!Text0))

Hi folk,

I have the following query in my db:

SELECT Sales.Invoice, Sales.Code, Sales.IDate
FROM Sales
WHERE (((Sales.IDate)=[forms]![form1]![text0]));

It works fine but I also want the query return all
records when user put "all" into the [forms]![form1]!
[text0]. Could anyone show me how to do it?

I tried something like this:

SELECT Sales.Invoice, Sales.Code, Sales.IDate
FROM Sales
WHERE (((Sales.IDate)=IIf([forms]![form1]![text0]="all",
(Sales.IDate) Like "*",[forms]![form1]![text0])));

But it didn't work.

Please help.

Thanks in advance.

Tim.

.