Solved

Converting As400 date formats YYMMDD and CYYMMDD for use in Access

Posted on 2012-03-11
12
1,202 Views
Last Modified: 2012-03-13
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
0
Comment
Question by:NEC293
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 39

Assisted Solution

by:als315
als315 earned 250 total points
ID: 37707044
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

0
 
LVL 57
ID: 37707056
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.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37707109
Or a one-liner:

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

/gustav
0
 

Author Comment

by:NEC293
ID: 37707477
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).
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37707715
If so, using CVDate should work:

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

/gustav
0
 
LVL 39

Expert Comment

by:als315
ID: 37708422
You can call function as:
DateAS400([As400DateField] & "")
If you don't like error message - comment it
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:NEC293
ID: 37711463
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.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 37712115
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, "@@@@/@@/@@")))

/gustav
0
 
LVL 39

Expert Comment

by:als315
ID: 37713143
You can use Gustav's method everywhere (I prefer isnull function):
IIF(isnull([As400DateField]), Null, DateAS400([As400DateField]))
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37713334
> 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
0
 

Author Closing Comment

by:NEC293
ID: 37714289
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.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37715096
Thanks for the feedback.

/gustav
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now