Null Date Value and String vs Date and #Error (1 Viewer)

BrokenBiker

ManicMechanic
Local time
Today, 17:56
Joined
Mar 22, 2006
Messages
128
I have a query w/a calculated filed (TngDue). Some of those records have no date. When I use CDate to convert the TngDue field to a date format, the records w/o date entries give me an #Error.

I've tried to use IIf and IsNull to get rid of the #Errors and still maintain a date format, but am so far unsuccessful.
Code:
TngDueDate: IIf(IsNull([TngDue]),"",CDate([TngDue]))

It seems that if I try to handle the null values to get rid of the #Errors, I have to live w/a text string. If I want a date value, I'll have to live w/the #Errors. Is there any way to handle null values w/a date field and still have the result as a date as opposed to a string?
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 09:56
Joined
Jan 20, 2009
Messages
12,849
The Null string is not valid in a date field.

TngDueDate: IIf(IsNull([TngDue]),Null,CDate([TngDue]))
 

BrokenBiker

ManicMechanic
Local time
Today, 17:56
Joined
Mar 22, 2006
Messages
128
Thanks for the reply, but that gave me the same #Error. Is there a way to use the IsDate fx? Possibly If Not (IsDate(TngDue)...etc....? I'll give it a shot and see what it does.
 

BrokenBiker

ManicMechanic
Local time
Today, 17:56
Joined
Mar 22, 2006
Messages
128
Yep, that did it!

TngDueDate: IIf(Not IsDate([TngDue]),Null,CDate([TngDue]))
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 09:56
Joined
Jan 20, 2009
Messages
12,849
Would seem that the apparently Null values were actually the Null String.
The IsDate is a good solution because it will catch anything that can't be converted.
 

LOYNN

New member
Local time
Today, 15:56
Joined
Sep 11, 2015
Messages
2
I just went thought this old thread and it looks quite helpful.

But I met a similar issue which I really don't understand.

There's a filed (let's call it A) came with Null Value and also date value. I am going to use this field to do a calculation but the result is quite strange. here comes the details.

Field B:IIF(NOT ISDATE[A],NULL,[A])
Field C: +5

With this calculation, those null values in Field C become #error and those with date values in column C become a date.

I changed Field B to IIF(NOT ISDATE([A]),NULL,[A]+5) and delete Field C. Those null values still keep null but those with date values become #error.

Anyone knows how to manage this? Thanks for help in advance
 

Users who are viewing this thread

Top Bottom