need to return Null in date field

[vicki.wells]

I'm working on a calculated field in a query in which I'm using an IIf
statement to return either the date from another field or Null,
depending on whether a Yes/No field is checked. It works except that
when the checkbox is checked I need it to return a blank in the date
field, and I'm getting #error. I'm using " " as the value to return if
the Yes/No is checked. Do I need to do it differently for a date field?

 

[Al Camp]

Vicki,
Try...
YourDateFieldName = Null

Your " " is entering a Space in the date field, which is not allowed...

 

[Allen Browne]

A null is not the same as a space or zero-length string.

Specify that you want Null, e.g.:
=IIf([MyYesNo] = True, Null, Date())

If JET still does not understand the field as a date, wrap the expression in
CVDate(), e.g.:
=CVDate(IIf([MyYesNo] = True, Null, Date()))

 

[vicki.wells]

Got, thanks!