Query by form with 3 parameters

[Kathy]

Hello, I am building a parameter query that uses a form to fill in 3
criteria. The criteria are Physician Name, Start Date, and End Date. The
physician name is a combo box and I have added the IS NULL to the OR part of
criteria in case the combo box is left blank. If left blank, then all records
will show. This part seems to be working correctly.
The Start Date and End Date uses BETWEEN...AND to display a range of records
from a single entry date field. I think I am having trouble with trying to
put in the IS NULL in case one or both of those criteria are left blank.
So at this juncture, if I put in the physician name and start date and end
date, the query works. If I leave the physician name blank, then all records
show. Both of these are working correctly.
But if I would like to see all the records from 1/1/2007 through 6/1/2007
for all the physicians (physician name left blank), then I get nothing. This
is where I am stumped...and any help is greatly appreciated. Thank you.

 

[Marshall Barton]

Hello, I am building a parameter query that uses a form to fill in 3
criteria. The criteria are Physician Name, Start Date, and End Date. The
physician name is a combo box and I have added the IS NULL to the OR part of
criteria in case the combo box is left blank. If left blank, then all records
will show. This part seems to be working correctly.
The Start Date and End Date uses BETWEEN...AND to display a range of records
from a single entry date field. I think I am having trouble with trying to
put in the IS NULL in case one or both of those criteria are left blank.
So at this juncture, if I put in the physician name and start date and end
date, the query works. If I leave the physician name blank, then all records
show. Both of these are working correctly.
But if I would like to see all the records from 1/1/2007 through 6/1/2007
for all the physicians (physician name left blank), then I get nothing. This
is where I am stumped...and any help is greatly appreciated. Thank you.

The Where clause should end up looking like:

WHERE (physicianfield = Forms!theform.txtPhysician
OR Forms!theform.txtPhysician Is Null)
AND (datefield <= Forms!theform.txtStart
OR Forms!theform.txtStart Is Null)
AND (datefield <= Forms!theform.txtEnd
OR Forms!theform.txtEnd Is Null)

I don't know how that would show up in the QBE.