Capturing Error when converting to Date

[BreadLord]

Hi there,

I am trying to

1) convert a text type field to a date type field (this I think I have completed)

2) take any resulting errors and bring back a specific value instead of '#Error'

Here is what I have so far:

IIf(IsError(DateSerial(Right(Left(JobOpenDate,10),4),Left(JobOpenDate,2),Right(Left([JobOpenDate],5),2)))=true, 0, DateSerial(Right(Left(JobOpenDate,10),4),Left(JobOpenDate,2),Right(Left([JobOpenDate],5),2)))

However, my output still shows '#Error' -- I am not sure why this is happening (it should show 0, correct?) Any assistance is appreciated...

 

[SkipVought]

Hi,

Exactly what value do you have in this text field?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BreadLord]

Right now, the fields that I am converting have date text, like "06-05-2004". Some are blank, and the DateSerial gives #Error output. I'd love to clean this up with a substitute value.


Thanks!

 

[PHV]

IIf(Trim(JobOpenDate & "")="", 0, DateSerial(Right(Left(JobOpenDate,10),4),Left(JobOpenDate,2),Right(Left([JobOpenDate],5),2)))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886