issue with old dates. 1

[kkson]

I am using the following code in a query to convert a text date to date field.

PUBDATECONVERT: CDate(Left([FIXPUBDATE],2) & "-" & Mid([FIXPUBDATE],3,3) & "-" & Mid([FIXPUBDATE],6,2))

However on dates that are the 31st date in the month i get an error. ie. 31sep65, and 31jun87 both of these months only have 30 days in them.

I can't use the isdate() command with the [fixpubdate] becuase it returns false due to it being a text field. I can't just remove a day on all dates that are the 31st. Is it possible to just remove a day on all the dates that cause the error? If i run the qry manually I can find the errors and change the [fixpubdate] to 1 day less and the error will go away. Any way to automate it. (not everyone that will use the program is familar with access.)

Thanks!

 

[PHV]

Perhaps this ?
PUBDATECONVERT: IIf(IsDate(Left([FIXPUBDATE],2) & "-" & Mid([FIXPUBDATE],3,3) & "-" & Mid([FIXPUBDATE],6,2)),CDate(Left([FIXPUBDATE],2) & "-" & Mid([FIXPUBDATE],3,3) & "-" & Mid([FIXPUBDATE],6,2)),CDate(Left([FIXPUBDATE],2)-1 & "-" & Mid([FIXPUBDATE],3,3) & "-" & Mid([FIXPUBDATE],6,2)))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kkson]

That worked great. Was thinking I had to do a combination. but wasn't sure on how.

Thanks.