NEC293
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or a one-liner:
datDate = CDate(Format(19000000 + YourAS400DateValue, "@@@@/@@/@@"))
/gustav
datDate = CDate(Format(19000000 + YourAS400DateValue, "@@@@/@@/@@"))
/gustav
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).
If so, using CVDate should work:
datDate = CVDate(IIf(IsNull(YourAS40 0DateValue ), Null, Format(19000000 + YourAS400DateValue, "@@@@/@@/@@")))
/gustav
datDate = CVDate(IIf(IsNull(YourAS40
/gustav
You can call function as:
DateAS400([As400DateField] & "")
If you don't like error message - comment it
DateAS400([As400DateField]
If you don't like error message - comment it
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can use Gustav's method everywhere (I prefer isnull function):
IIF(isnull([As400DateField ]), Null, DateAS400([As400DateField] ))
IIF(isnull([As400DateField
> 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
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
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.
Thanks for the feedback.
/gustav
/gustav
Function CorrectDate(strDateValue As String) As Variant
Dim strDate As String
On Error GoTo CorrectDate_Error
strDate = Format$(CLng(strDateValue)
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.