How can click 'Cancel' on parameter query prompt to have all the .

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a parameter query, and it runs great. But sometimes I just
want a report of everything instead of part of the reprot based on the
parameter. Is there any way that I can set the parameter prompt so when I
click 'Cancel', the report will still run and the query will pull all records
just like I did not set the parameter at all.
Thanks a lot in advance.
 
I have created a parameter query, and it runs great. But sometimes I just
want a report of everything instead of part of the reprot based on the
parameter. Is there any way that I can set the parameter prompt so when I
click 'Cancel', the report will still run and the query will pull all records
just like I did not set the parameter at all.
Thanks a lot in advance.
Click to expand...

No, not by cancelling the prompt.
But you can by clicking OK without any entry if you have coded the
criteria property:

Where YourTable.[LastName] = [Enter LastName] Or YourTable.[LastName]
Like IIf IsNull([LastName]), "*")

Will return either the record that matches the parameter prompt, or if
the prompt is blank, all the records.
 
PMFBI

This is "picky" I know, but...
there is a gotcha in using wildcard with LIKE.

WHERE YourTable.[LastName] LIKE "*"

will return "all records" where LastName is not null
or LastName is a zero-length string (i.e., "").

*** It will not return the records where LastName = Null. ***

If you have records where the value of LastName may be Null,
and you want to return these records also
(i.e., all of the records), suggest use:

WHERE YourTable.[LastName] = [Enter LastName] Or [Enter LastName] IS NULL

apologies for butting in,

Gary Walter

fredg said:
I have created a parameter query, and it runs great. But sometimes I just
want a report of everything instead of part of the reprot based on the
parameter. Is there any way that I can set the parameter prompt so when I
click 'Cancel', the report will still run and the query will pull all records
just like I did not set the parameter at all.
Thanks a lot in advance.
Click to expand...

No, not by cancelling the prompt.
But you can by clicking OK without any entry if you have coded the
criteria property:

Where YourTable.[LastName] = [Enter LastName] Or YourTable.[LastName]
Like IIf IsNull([LastName]), "*")

Will return either the record that matches the parameter prompt, or if
the prompt is blank, all the records.
Click to expand...
 
fredg said:
I have created a parameter query, and it runs great. But sometimes I just
want a report of everything instead of part of the reprot based on the
parameter. Is there any way that I can set the parameter prompt so when I
click 'Cancel', the report will still run and the query will pull all records
just like I did not set the parameter at all.
Thanks a lot in advance.
Click to expand...


No, not by cancelling the prompt.
But you can by clicking OK without any entry if you have coded the
criteria property:

Where YourTable.[LastName] = [Enter LastName] Or YourTable.[LastName]
Like IIf IsNull([LastName]), "*")

Will return either the record that matches the parameter prompt, or if
the prompt is blank, all the records.
Click to expand...

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

I've got a different criteria to get ALL when parameter is NULL. This
is from an MS example db "qrysmp97.mdb."

SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate,
Orders.ShipCountry
FROM Orders
WHERE [Enter the country: Example: 'USA'] Is Null OR
(Orders.ShipCountry=[Enter the country: Example: 'USA'] AND [Enter the
country: Example: 'USA'] Is Not Null)

The template is:

Parameter IS NULL OR
(column_name = Parameter And Parameter IS NOT NULL)

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

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

iQA/AwUBQhKf3YechKqOuFEgEQLlPQCgz7j4M1kS6x5vb1yhbsFyINbWPRUAn01d
y3TsifYTarT9Q4sq+hy5uTZV
=YCrS
-----END PGP SIGNATURE-----
 
MGFoster said:
I've got a different criteria to get ALL when parameter is NULL. This
is from an MS example db "qrysmp97.mdb."

SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate,
Orders.ShipCountry
FROM Orders
WHERE [Enter the country: Example: 'USA'] Is Null OR
(Orders.ShipCountry=[Enter the country: Example: 'USA'] AND [Enter the
country: Example: 'USA'] Is Not Null)

The template is:

Parameter IS NULL OR
(column_name = Parameter And Parameter IS NOT NULL)
Click to expand...

Hi MG,

Interesting. I was trying to think through the advantage
of this, then flashed on the fact that I couldn't think of
a single app that I have written that uses any of these
techniques. 8-)

I wonder if any of us would give up the control
one gets by using a form where the user enters/selects
the "parameters" through controls on the form, then
everything gets checked and where clauses get built
through code behind a command button.

ywc, go to Rogers Sample db website and learn
filter by form.

http://www.rogersaccesslibrary.com/TableOfContents3.asp

Running a query allowing users to enter "anything"
in a parameter prompt will foster (no pun intended) a new
found respect for the creativity of your users!

Gary Walter
 
Back
Top