Date Parameter

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

Guest

I understand that to use a date parameter you must use Between....
But is there a way to set a parameter that allows you the option of
selecting null or not null dates without put "null" of "Is Not Null" in the
criteria?
 
I would like to have the option of choosing null or not null - by not
creating 2 queries. I had to ask - thanks
 
Melissa,

You could create a form that has a combo box on it that has "Is NULL" and "Is not NULL" as 2 options in it. Then you could set up
your criteria in your query to reference that combo box. In the criteria you would enter:

[forms]![Name of Form]![Name of Combo Box]

When you run this query, make sure the form is open (it can be hidden though). You should be able to put a button on this form that
will run your query for you.

I hope this helps,

Conan Kelly
 
I understand that to use a date parameter you must use Between....
But is there a way to set a parameter that allows you the option of
selecting null or not null dates without put "null" of "Is Not Null" in the
criteria?
Click to expand...

You don't have to use BETWEEN. In fact, I'll often use a criterion
such as

WHERE (
[Datefield] >= CDate(NZ([Forms]![Formname]![txtStart],"1/1/100"))
AND
([Datefield] < DateAdd("d", 1,
CDate(NZ([Forms]![Formname]![txtEnd],"12/30/9999")))


John W. Vinson[MVP]
 
Curious as to why not just

WHERE (
[Datefield] BETWEEN CDate(NZ([Forms]![Formname]![txtStart],"1/1/100"))
AND DateAdd("d", 1,CDate(NZ([Forms]![Formname]![txtEnd],"12/30/9999"))))

(BTW, I think you're missing a terminating parenthesis: there should be 4 at
the end.)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


John Vinson said:
I understand that to use a date parameter you must use Between....
But is there a way to set a parameter that allows you the option of
selecting null or not null dates without put "null" of "Is Not Null" in
the
criteria?
Click to expand...

You don't have to use BETWEEN. In fact, I'll often use a criterion
such as

WHERE (
[Datefield] >= CDate(NZ([Forms]![Formname]![txtStart],"1/1/100"))
AND
([Datefield] < DateAdd("d", 1,
CDate(NZ([Forms]![Formname]![txtEnd],"12/30/9999")))


John W. Vinson[MVP]
Click to expand...
 
Curious as to why not just

WHERE (
[Datefield] BETWEEN CDate(NZ([Forms]![Formname]![txtStart],"1/1/100"))
AND DateAdd("d", 1,CDate(NZ([Forms]![Formname]![txtEnd],"12/30/9999"))))

(BTW, I think you're missing a terminating parenthesis: there should be 4 at
the end.)
Click to expand...

I guess I habitually use the >= and < syntax instead of BETWEEN to
allow for "OR IS NULL" - which wasn't included in this particular
answer. You're right, it's simpler and the NZ() takes care of that
issue!

John W. Vinson[MVP]
 
Back
Top