Null Date Value and String vs Date and #Error

[BrokenBiker]

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.

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]

The Null string is not valid in a date field.

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

 

[BrokenBiker]

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]

Yep, that did it!

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

 

[Galaxiom]

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]

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

 

[obeylele]

Are you trying to add 5 days to date?

if so try DateAdd function

Field B = IIF(NOT ISDATE([A]), NULL, DateAdd ("d", 5, [A]))

refer to this
http://www.techonthenet.com/access/functions/date/dateadd.php

 

[LOYNN]

It did works!! Thank you very much!