Parameter query criteria for null dates

[John Baker]

I am trying to create a parameter query based on a date field but am having
problems with the criteria. The parameter is a textbox on the form which
calls the query.

I would like to get all records which have a null date if the textbox on the
form contains Null.
I have been trying the following criteria without success:

iif(isnull([forms]![frmReport]![tbxStartDate]),Null)



Any help greatly appreciated.

John Baker
(e-mail address removed)

 

[Michel Walsh]

Hi,


The iif( ) has three arguments, you are missing one, but noneless, since
null=null returns null, it would probably not work as you expect.


? null = null
null



In SQL view, try:

WHERE ((myField IS NULL) AND (FORMS!frmReport!tbxStartDate IS NULL))
OR (myField=FORMS!frmReport!tbxStartDate)


Hoping it may help,
Vanderghast Access MVP

 

[John Baker]

Oh thank you so much Michel

I have been pulling my hair out trying to solve my problem.
It was actually more complex than I listed but your suggestion was exactly
what I needed to use to get me through.
Thank you so much!

John Baker
(e-mail address removed)

Hi,


The iif( ) has three arguments, you are missing one, but noneless, since
null=null returns null, it would probably not work as you expect.


? null = null
null



In SQL view, try:

WHERE ((myField IS NULL) AND (FORMS!frmReport!tbxStartDate IS NULL))
OR (myField=FORMS!frmReport!tbxStartDate)


Hoping it may help,
Vanderghast Access MVP

I am trying to create a parameter query based on a date field but am having
problems with the criteria. The parameter is a textbox on the form which
calls the query.

I would like to get all records which have a null date if the textbox on the
form contains Null.
I have been trying the following criteria without success:

iif(isnull([forms]![frmReport]![tbxStartDate]),Null)



Any help greatly appreciated.

John Baker
(e-mail address removed)