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..
Who is Participating?
Gustav BrockConnect With a Mentor CIOCommented:
There is a way.
This expression will return the date for input values representing a date and Null for input values of 0 or Null:

= CVDate(IIf(Nz(YourAS400DateValue, 0) = 0, Null, Format(19000000 + YourAS400DateValue, "@@@@/@@/@@")))

als315Connect With a Mentor Commented:
Try this function (it should be placed in module):
Public Function DateAS400(A As String) As Date
Dim D As Integer, M As Integer, Y As Integer, L As Integer
L = Len(A)
Y = 1900
If L < 6 Then
    MsgBox "Error in Date value", vbOKOnly
    DateAS400 = 0
    Exit Function
ElseIf L = 7 Then
    Y = 2000
    A = Right(A, 6)
End If
Y = Y + CInt(Mid(A, 1, 2))
M = CInt(Mid(A, 3, 2))
D = CInt(Mid(A, 5))
DateAS400 = DateSerial(Y, M, D)
End Function

Open in new window

Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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))

  On Error Resume Next

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

7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

Gustav BrockCIOCommented:
Or a one-liner:

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

NEC293Author Commented:
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 BrockCIOCommented:
If so, using CVDate should work:

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

You can call function as:
DateAS400([As400DateField] & "")
If you don't like error message - comment it
NEC293Author Commented:
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.
You can use Gustav's method everywhere (I prefer isnull function):
IIF(isnull([As400DateField]), Null, DateAS400([As400DateField]))
Gustav BrockCIOCommented:
> 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.

NEC293Author Commented:
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 BrockCIOCommented:
Thanks for the feedback.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.