iSeries Date in Imported Data ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

When working with an IBM iSeries (IBM AS/400) mainframe computer and importing raw data into Microsoft Access for custom report preparation, an issue often arises. The date values in the data records are frequently stored as plain numbers.

These can appear in formats such as mmddyyyy (e.g., 07092011), ddmmyyyy (e.g., 09072011), or yyyymmdd (e.g., 20110709). Such values cannot be used directly as dates in queries or other analysis tasks. They must first be converted into proper date values before they can be utilized effectively.

Let us try one or two conversion examples.

Example-1: mmddyyyy

X = 07092011 or x=”07092011”

Y = July 09, 2011 (internal representation of the actual date number is 40733)

Conversion expression:

Y = DateSerial(Right(x,4),Left(x,2),Mid(x,3,2))

y = DateValue(Left(x,2) & "-" & Mid(x,3,2) & "-" & Right(x,4))

Depending on the input date format (Asian, USA, or ANSI), the order in which you use the Left(), Right(), and Mid() functions within the DateSerial() or DateValue() functions will vary. However, writing these expressions repeatedly in every query is not practical and quickly becomes time-consuming. The simplest solution is to create a user-defined function with the required expression and call that function from the query column—or from anywhere else the numeric date values are used in calculations or comparisons.

The DMY_N2D() Function.

Let us create the following simple Functions that will make our lives easier with these numbers in the long run.

Function: DMY_N2D(ByVal ddmmyyyy as Variant) As Date

This function converts a date number in ddmmyyyy format into a valid date value. The input can be provided either as a long number (e.g., 09072011 or 9072011) or as text (e.g., "09072011"). The suffix _N2D stands for Number to Date. The first three characters in the function name (DMY) indicate the order of the day, month, and year segments in the input date number.

Public Function DMY_N2D(ByVal ddmmyyyy As Variant) As Date

'------------------------------------------------------------------
'Converts Numbers (in ddmmyyyy format) 09072011 into a Date Number
'Author : a.p.r.pillai
'Date   : Sept. 1999
'Rights : All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------------------
Dim strN2D As String

On Error GoTo DMY_N2D_Err

strN2D = Format(ddmmyyyy, "00000000") ' add 0 at the left side, if 7 digit number

DMY_N2D = DateSerial(Right(strN2D, 4), Mid(strN2D, 3, 2), Left(strN2D, 2))

DMY_N2D_Exit:
Exit Function

DMY_N2D_Err:
MsgBox Err.Description,, "DMY_N2D()"
Resume DMY_N2D_Exit
End Function

The MDY_N2D() Function.

When the input Number is in mmddyyyy format:

Public Function MDY_N2D(ByVal mmddyyyy As Variant) As Date 
'------------------------------------------------------------------ 
'Converts Numbers (in mmddyyyy format) 07092011 into a Date Number 
'Author : a.p.r.pillai 
'Date   : Sept. 1999 
'Rights : All Rights Reserved by www.msaccesstips.com 
'------------------------------------------------------------------
Dim strN2D As String 

On Error GoTo MDY_N2D_Err 

strN2D = Format(mmddyyyy, "00000000") ' add 0 at the left side, if 7 digit number

MDY_N2D = DateSerial(Right(strN2D, 4), Left(strN2D, 2), Mid(strN2D, 3, 2)) 

MDY_N2D_Exit: 
Exit Function 

MDY_N2D_Err: 
MsgBox Err.Description,, "MDY_N2D()" 
Resume MDY_N2D_Exit 
End Function

The YMD_N2D() Function.

When the date number is in yyyymmdd (ANSI) format:

Public Function YMD_N2D(ByVal yyyymmdd As Variant) As Date
'------------------------------------------------------------------
'Converts Numbers (in yyyymmdd format) 20110709 into a Date Number
'Author : a.p.r.pillai
'Date   : Sept. 1999
'Rights : All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------------------
Dim strN2D As String

On Error GoTo YMD_N2D_Err

YMD_N2D = DateSerial(Left(strN2D, 4),Mid(strN2D, 5, 2),Right(strN2D, 2))

DMY_N2D_Exit:
Exit Function

YMD_N2D_Err:
MsgBox Err.Description,, "YMD_N2D()"
Resume YMD_N2D_Exit
End Function