#Error return value in date field HELP

Question

Hello,

I have a date/time field called "compdatetime" in my database. There are blank record in the compdate field.

I am trying to convert the date/time stamp field to just date only which I use compdate=DateValue(compdatetime).

I worked only the records with value. Any record without value is returns #ERROR.

How I can fixed this problem so instead of return #ERROR, return blank/ empty? I tried IIF statement, but it didn't work.

=IIF(compdatetime is not null, DateValue(compdatetime),"")

This does return blank on record without value, except it return the date as text field. THANKS

Answer 1

Try this --

=IIF(IsDate([compdatetime]), DateValue(compdatetime),"")

           Or --

=IIF(IsDate([compdatetime]), DateValue(compdatetime), Null)

Answer 2

=IIF(compdatetime is not null, DateValue(compdatetime),"")

Try incorporating the ISNULL() function in the criteria of your IIF() function.

SELECT IIF(ISNULL(dt.compdatetime),"", DATEVALUE(dt.compdatetime)) As [Just the Date]

FROM tblDateTime AS dt;

Answer 3

Thank Karl,

The second option worked. The first didn't.