asked on

Converting As400 date formats YYMMDD and CYYMMDD for use in Access

Dear Gurus:
I need to convert dates imported from an AS400 server into an Access 2003 database. The dates are both pre-2000 and post 2000 with a Century notation on the post-2000 years.
I'm not a programmer, but I do know how to apply and use Module code.

I would like Module code to handle either case:
991204 needs to become 12/04/1999 and
1021204 needs to convert to 12/04/2002
I've found several examples - but none seem to handle both pre- and post-2000.
Is this possible??

I have a report with 12 different dates that need to be converted and also need to work in calculations, and I am really stuck...
Thanks in advance..
NEC

SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Dettman (EE MVE)

Here's a routine that will do the job:
Function CorrectDate(strDateValue As String) As Variant

Dim strDate As String

On Error GoTo CorrectDate_Error

strDate = Format$(CLng(strDateValue), "0000000")

CorrectDate = DateSerial(1900 + Left$(strDate, 3), Mid$(strDate, 4, 2), Mid$(strDate, 6, 2))

CorrectDate_Exit:
On Error Resume Next

Exit Function

CorrectDate_Error:
MsgBox "Error occured"
CorrectDate = Null
Resume CorrectDate_Exit

End Function

You can call it from a query and it will return a variant with a date value in it. You can then format that to whatever you need.

Jim.

Gustav Brock

Or a one-liner:

datDate = CDate(Format(19000000 + YourAS400DateValue, "@@@@/@@/@@"))

/gustav

NEC293

ASKER

All seem to work - except when the field has no date. Then the error handling takes over. I apologize - I forgot to mention that some fields would not contain a date (zero value).

Gustav Brock

If so, using CVDate should work:

datDate = CVDate(IIf(IsNull(YourAS400DateValue), Null, Format(19000000 + YourAS400DateValue, "@@@@/@@/@@")))

/gustav

als315

You can call function as:
DateAS400([As400DateField] & "")
If you don't like error message - comment it

NEC293

ASKER

ALS315 - Thank you for sticking with me!
The call function and commenting-out worked well...just one little problem left - when the date is "zero" the date conversion value is "12:00:00AM".
I changed the format of the query value to be SHORT DATE and the conversion becomes "12/30/1899".
Is there any way to code the module to make these fields blank - or even a zero value is fine.
I can find a work-around, but I'd like to have the conversion handled in the module.

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

als315

You can use Gustav's method everywhere (I prefer isnull function):
IIF(isnull([As400DateField]), Null, DateAS400([As400DateField]))

Gustav Brock

> I prefer isnull function ..

Sorry, I didn't read carefully, I understood some fields could have a zero, but that seems not to be the case. Thus, as the field contains either Null or a date expression, please disregard my previous post.

/gustav

NEC293

ASKER

I split the points because both experts provided not only the solution - but some good guidence. I have applications where both solutions can work beautifully while providing much needed improvements to the "inherited" database that I am using.

Gustav Brock

Thanks for the feedback.

/gustav