datevalue returning #Error

[Guest]

I have a date/time field. I need to extract just the date in order to do
further date calculations. I created a calculated field using DateValue - but
if the field does not have a date, it returns #Error. So, I changed it to the
following: IIF([field] is null,"",DateValue([field])) - this leaves out the
error but now changes all the dates to text. A workaround is to have it enter
a 0 rather than leaving it blank, but then that enters the value of 12:00:00
am. Any other suggestions of how not to have the error display but prevent
the dates from changing to text?

 

[Allen Browne]

The zero-length string is the problem: that forces Access to treat the value
as text.

Instead, use Null. And wrap the expression in CVDate() to ensure it is
understood as a date:
CVDate(IIF([field] is Null, Null, DateValue([field])))

You could also use Fix() to lose the time component:
CVDate(Fix([field]))

 

[Guest]

A huge thank you!! That should do it!
--
maryj

The zero-length string is the problem: that forces Access to treat the value
as text.

Instead, use Null. And wrap the expression in CVDate() to ensure it is
understood as a date:
CVDate(IIF([field] is Null, Null, DateValue([field])))

You could also use Fix() to lose the time component:
CVDate(Fix([field]))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a date/time field. I need to extract just the date in order to do
further date calculations. I created a calculated field using DateValue -
but
if the field does not have a date, it returns #Error. So, I changed it to
the
following: IIF([field] is null,"",DateValue([field])) - this leaves out
the
error but now changes all the dates to text. A workaround is to have it
enter
a 0 rather than leaving it blank, but then that enters the value of
12:00:00
am. Any other suggestions of how not to have the error display but prevent
the dates from changing to text?