parameter query on two fields

  • Thread starter Thread starter Tom Nichol
  • Start date Start date
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
 
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-----
 
Back
Top