Why do I get an error in a query field when error handling is built in?

[rhinodance]

I'm doing a query and I have a field called UPDTE with entries that look like '20120801' in which a date in stored as a number. So '20120801' represents Aug 1, 2102. In the query I've defined a field where I'm turning this number into a date by using the CDATE function. The basic formula I'm starting with is this:

Update_Dt: CDate(Mid([UPDTE],5,2) & "/" & Right([UPDTE],2) & "/" & Left([UPDTE],4))

It works fine when UPDTE is populated. However, sometimes UPDTE is blank so trying to transform it into a date returns an error. To cover this possibility, I've expanded the formula to test for an error first and if an error is returned the result should simply be null (as represented by ""). Here's my expanded formula with error handling:

Update_Dt: IIf(IsError(CDate(Mid([UPDTE],5,2) & "/" & Right([UPDTE],2) & "/" & Left([UPDTE],4))),"",CDate(Mid([UPDTE],5,2) & "/" & Right([UPDTE],2) & "/" & Left([UPDTE],4)))



The problem is, when I run the query I get #ERROR for these blank fields instead of a null. How can my formula return #ERROR for these blanks and not null- since I'm testing for an error? Thanks.

[orange]

Why don't you use a standard Date datatype ?? Why play with strings and numbers and attempt to make it a Date?
There are so many things to learn and experiment with and understand ...............

Or is this some academic exercise?

[JoeM]

Just check to see if the value is null before doing anything to it, i.e.
Update_Dt: Iif(IsNull([UPDTE]),"",CDate(Mid([UPDTE],5,2) & "/" & Right([UPDTE],2) & "/" & Left([UPDTE],4)))

[rhinodance]

Joe: Thanks that worked. The non-date entries are actually '0' so I tested for that rather than null. I was using the ISERROR construct to cover any kind of entry into the UPDTE field that would not convert to a date though.

Orange: If I could I would, but I don't have control over the data I'm querying. The field is from a third party an App which stores the date as a number. I then have to convert it to a date for reporting purposes.