Is Null Parameter Criteria, Not Working

[ScottA]

I'm using instructions from a Microsoft knowledge base
article (209645) to set query parameters using a form
interface. My interface has two date fields (start and
end dates), and a combo box that filters by Department.

The KB tells me that it is possible to use the following
statement:

Between Forms!FormName!StartDate And Forms!FormName!
EndDate Or_
Forms!FormName!StartDate Is Null

The query runs fine if I remove the Is Null part of the
statement, or when a start date is provided, however, the
article explicitly describes the Is Null statement, and
tells me that I can use this criteria statement to query
a date field by using Start Date and End Date text boxes
on the query form.... If you omit a start date value on
the form, however, the query returns all records,
regardless of the End Date value.

My query returns no records.

Can anyone provide an explanation/solution?

Thanks!

Scott

 

[Steve Schapel]

Scott,

The expression you have used is correct, assuming you haven't put the
_ character in your query criterion. With this expression, if the
StartDate textbox is left blank, I would expect the query to return
all records.

Normally if you put an expression like this in the query criteria,
when you save the query Access will re-arrange it for you, so you will
end up with the criteria in your date field simply showing
Between Forms!FormName!StartDate And Forms!FormName!EndDate
and then a new calculated field in the query, like...
Expr1: Forms!FormName!StartDate
with Is Null in the second criteria row of the query design grid. Is
this what happens with yours?

- Steve Schapel, Microsoft Access MVP

 

[Van T. Dinh]

Could be just the typo in the post but we don't use underscore as
continuation character in SQL String.

Also I would use parentheses to make sure there is no mix-up in the order of
operation. Something like:

(Between Forms!FormName!StartDate And
Forms!FormName!EndDate) OR
(Forms!FormName!StartDate Is Null)