Null Dates and Reports

[Rabiddog]

I have a problem displaying a date on a report - the query yields a date, but when the date is NULL it displays 30 Dec 1899, I would like it to display nothing - an empty string, i have tried using the Iif statementin the report like:
=IIf([Date cleared]=#18/12/1899#,' ',[date cleared])
but then I just get an error what is the right way to deal with this annoying little problem?

 

[SgtJarrow]

I would try this:

=IIf([Date cleared]=#18/12/1899#,Null,[date cleared])

or

=IIf([Date cleared=0,' ',[date cleared])

or

=IIf([Date cleared]=0,Null,[date cleared])

But I still don't know if it will work. You are trying to put an empty value into a numeric field...remember dates are truly stroed as numbers..the date is the integer portion and the time is the decimal portion. If no number is given, a zero is assumed, which is why you get the date you get.

How are you getting the date?? Is it calculated? If so, you could fix your function to return a Null or Empty String, depending on the situation. -------------------------------------
Only two things are infinite, the
universe and human stupidity, and
I'm not sure about the former.
(Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: mstrmage@tampabay.rr.com

 

[Rabiddog]

Hi
thanks, I have done this and it works, the problem is I quess, I tried this earlier and for some reason the change was not saved - it looked like it was, but the form took the original, values- I only discovered this when I tried to fix a syntax error which the form kept complaining about in the date control - but there wasnt one ... it wasn't until I deleted the date bound text box from the form and recreated it. It could because I am firing the report via automation from a C++ front end and maybe I had a sleeping MSAccess process lying around - I don't know but it sure confused me! Have you seen anything like it?

once agian thank you for your help!

Terry