parameter query on two fields

T

Tom Nichol

I am using Access 2000. I have created a Parameter query
that prompts on two different fields. When I type values
at both prompts, the appropriate records are returned.
When I leave one prompt blank, I get NO records. I would
like to have the option of seeing all records for only one
of these values without writing a whole new query. Surely
this CAN be done. The Access Help screens do not seem to
cover what seem to me should be a fairly common
application of the parameter query.

Would someone be so kind as to bring me up to speed on
this. It would be very much appreciated.

Many Thanks
 
M

MGFoster

Tom said:
I am using Access 2000. I have created a Parameter query
that prompts on two different fields. When I type values
at both prompts, the appropriate records are returned.
When I leave one prompt blank, I get NO records. I would
like to have the option of seeing all records for only one
of these values without writing a whole new query. Surely
this CAN be done. The Access Help screens do not seem to
cover what seem to me should be a fairly common
application of the parameter query.

Would someone be so kind as to bring me up to speed on
this. It would be very much appreciated.
Click to expand...

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

This is a common question. You can find various answers on Google group
search.

Here's one of mine (which I copped from an MS sample db):

PARAMETERS [Enter Date] Date;
SELECT ...
FROM ...
WHERE [Enter Date] Is Null OR
([Enter Date] Is Not Null and DateColumn = [Enter Date])

If the parameter [Enter Date] is NULL all records are returned;
otherwise, only records where the DateColumn = [Enter Date].

Explanation: Try a query w/ a WHERE clause like this:

WHERE True

All records in the table should be returned. Conversely, "WHERE False"
will have a resultset of no records.

That's what happens in the WHERE clause I gave as an example. Say
[Enter Date] is null (the user just clicked the OK button):

[Enter Date] Is Null = True

This:

[Enter Date] Is Not Null = False
AND
DateColumn = [Enter Date] = NULL

evaluates to FALSE: False And NULL = False

Therefore,

True OR False = True - All records are returned.

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

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

iQA/AwUBQHSqpIechKqOuFEgEQKmJwCg7xmT0ktO1a33i3pSXBY2jqw81iwAnjZU
dHMV3fLwu+7gWOK7tJK+o4GX
=0/hN
-----END PGP SIGNATURE-----
 
Top