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 with DateSerial)

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...

 

[raskew]

Hi -

Welcome to the forum.

I'm not sure exactly how you are inputting your text date (an example would be helpful). In most cases, the DateValue() function will accurately convert your text date.

Here are a couple of examples from the debug (immediate) window:

**********************************************
x = "June 24 2011"
y = "06/24/11"

? datevalue(x)
6/24/2011

? datevalue
6/24/2011

**********************************************

Hope that helps.

Bob