AS/400 Dates to Excel 3

[sunmorgus]

Hello. I am using ADO to import some data from an AS400 table to an excel worksheet, and I am having some trouble getting the dates to convert properly. When the date gets imported, it comes in in this format: 122005, which to me would mean December 20, 2005. However, when I change the number formatting of the cell to mm/dd/yy I get 01/13/34. Could anyone help me figure out why this would be? Thanks in advance for any help, and if you would like to see my code, just let me know. Thanks!

 

[SkipVought]

Hi, FYI... Why do Dates and Times seem to be so much trouble? faq68-5827

Therefore, you will need to take your numeric string and parse it into year, month, day in the DateSerial function.

Alternatively, format your SQL code to create a yyyy/mm/dd format that Excel will "like".



Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[sunmorgus]

how would I format my sql to do so? here is my current statement...

strSQL = "SELECT FZUNIT as UNIT#, FZSTAT as ST, FZDV# as DIV, FZDATE as DATE, FZCK# as INVOICE#, FZAGNT as TRUCK, " & _
"FZVNDR as STOP, FZCITY as CITY, FZQTY1 as GALLONS, FZAMT1 as TOTAL_COST " & _
"FROM IESFILE.FZFUEL

 

[sunmorgus]

Ok, I changed FZDATE as DATE to:
Mid(FZDATE, 3, 2)+'/'+Mid(FZDATE, 5, 2)+'/'+Left(FZDATE, 2) as DATE

but I get an error, the AS400 does not recognize mid, so how would I go about getting this to work? I would really like to be able to do it in the SQL statement, as I am eventually going to convert it to only read a user defined date range...

 

[fredericofonseca]

select cast(fzdate as char(10)) as fzdate should do the trick parcially.

The date format will depend on several things, namely on what driver you are using to connect to the AS/400, and on how your connection string properties are set.

Assuming the date is set to be returned as a YYYY-MM-DD you could do
select substr(cast(fzdate as char(10)),1,4)||'/'||substr(cast(fzdate as char(10)),6,2)||'/'||substr(cast(fzdate as char(10)),9,2) as fzdate should do the trick parcially.

to convert it into a format Excel understands better.
Just an example as I said.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[PHV]

Doesn't Excel understand YYYY-MM-DD ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sunmorgus]

Thanks Frederico! That worked perfectly. Star for you!

PHV...I tried every variation of yyyy-mm-dd mm/dd/yyyy and so forth, but I believe excel is not able to format it because it is being imported as a number, not a date...

 

[sunmorgus]

Ok, new question...because the original field on the as400 side is an integer (not sure whether large or small) there is no preceding 0 on the first 9 months (i.e. 011006 returns as 11006, so when the substr code runs, it returns 11/00/6, which is not good). I tried using a case statement in the sql to change the substr statement to adjust for 6 or 5 digit lengths, as shown in the code below, but it does not recognize a length of 5 for some reason (note, I put my two substr statments into variables)...

strSQL6 = "SELECT FZUNIT as UNIT#, FZSTAT as ST, FZDV# as DIV, " & _
            "CASE LENGTH(FZDATE) " & _
            "WHEN 6 THEN " & strSQLDate6 & _
            "WHEN 5 THEN " & strSQLDate5 & _
            " END as DATE, FZCK# as INVOICE#, FZAGNT as TRUCK, " & _
            "FZVNDR as STOP, FZCITY as CITY, FZQTY1 as GALLONS, FZAMT1 as TOTAL_COST " & _
            "FROM IESFILE.FZFUEL "

I also tried running 2 different queries, using a WHERE statement to only find a lenght of 6 and then a length of 5, but apparently it can only find a length of 6...

strSQL6 = "SELECT FZUNIT as UNIT#, FZSTAT as ST, FZDV# as DIV, " & _
            strSQLDate6 & _
            " as DATE, FZCK# as INVOICE#, FZAGNT as TRUCK, " & _
            "FZVNDR as STOP, FZCITY as CITY, FZQTY1 as GALLONS, FZAMT1 as TOTAL_COST " & _
            "FROM IESFILE.FZFUEL " & _
            "WHERE LENGTH(FZDATE) = 6"
strSQL5 = "SELECT FZUNIT as UNIT#, FZSTAT as ST, FZDV# as DIV, " & _
            strSQLDate5 & _
            " as DATE, FZCK# as INVOICE#, FZAGNT as TRUCK, " & _
            "FZVNDR as STOP, FZCITY as CITY, FZQTY1 as GALLONS, FZAMT1 as TOTAL_COST " & _
            "FROM IESFILE.FZFUEL " & _
            "WHERE LENGTH(FZDATE) = 5"

What am I doing wrong?

 

[fredericofonseca]

if you dates are stored as numerics and not dates, and on a "crap" format as you say, you should try and change that as a mater of principle. ALL dates should be stored as either dates or with a format that allows a proper sort on that field, e.g. CCYYMMDD instead of the american format many people use.

If they are numerics you should do the following then.


select substr(digits(my_numeric_field),1,2)||'/'||substr(digits(my_numeric_field),3,2)||'/'||substr(digits(my_numeric_field),5,2) as my_date


This assumes a field with a date on format yymmdd. Change the above according to your own data.


But to give you a better answer please do a "dspffd" on the file you are trying to import, and show us the output information for that particular field.
It should look something like

Field Level Information                                                       
             Data        Field  Buffer    Buffer        Field    Column       
  Field      Type       Length  Length  Position        Usage    Heading      
  REFCAT     CHAR            3       3         1        Both     REFERENCE    
                                                                 CATEGORY     
    Field text  . . . . . . . . . . . . . . . :  REFERENCE CATEGORY           

or

            Data        Field  Buffer    Buffer        Field    Column       
 Field      Type       Length  Length  Position        Usage    Heading      
 RQSDAT     PACKED       8  0       5        38        Both     REQUEST      
                                                                DATE         
   Field text  . . . . . . . . . . . . . . . :  REQUEST DATE                 
   Referenced information

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[sunmorgus]

Here is the output of dspffd on my file. I will try the other statement above in the meantime. Thanks!

 Field Level Information                                                     
              Data        Field  Buffer    Buffer        Field    Column     
   Field      Type       Length  Length  Position        Usage    Heading    
   FZCDE      CHAR            3       3         1        Both     CO. CODE   
     Field text  . . . . . . . . . . . . . . . :  FUEL CO. CODE              
     Coded Character Set Identifier  . . . . . :     37                      
   FZCARD     CHAR           20      20         4        Both     CARD NO.   
     Field text  . . . . . . . . . . . . . . . :  CARD NUMBER                
     Coded Character Set Identifier  . . . . . :     37                      
   FZCK#      CHAR           10      10        24        Both     INVOICE NO.
     Field text  . . . . . . . . . . . . . . . :  INVOICE NUMBER             
     Coded Character Set Identifier  . . . . . :     37                      
   FZDATE     ZONED        6  0       6        34        Both     INV. DATE  
     Field text  . . . . . . . . . . . . . . . :  INVOICE DATE               
   FZTIME     ZONED        4  0       4        40        Both     INV. TIME  
     Field text  . . . . . . . . . . . . . . . :  INVOICE TIME               
   FZDVCD     CHAR            6       6        44        Both     DRIVER     
                                                                  CODE       
     Field text  . . . . . . . . . . . . . . . :  DRIVER CODE

 

[sunmorgus]

Your revised statement did the trick. Thanks again for your help. I am totally new to the as400 world, and this was my first foray into pulling data with vba. Star for you!

 

[SkipVought]

Typical mainframe flat file-based coding.

The numbers are basically equivalent to a String of Digits that represent a date. As described above, either in the query or in VB, convert the digits to a Date String. Then convert the Date String to a Date Serial value ...

    Dim sDate As String, dDate As Date, iCent as integer
    n = 10406
    sDate = Format(n, "000000")
    if right(sdate,2)>"50" then 
      icent = 1900
    else
      icent = 2000
    end if
    dDate = DateSerial(iCent+ CInt(Right(sDate, 2)), CInt(Left(sDate, 2)), CInt(Mid(sDate, 3, 2)))
    MsgBox dDate

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[SkipVought]

what I meant to state...

    Dim sDate As String, dDate As Date, iCent as integer
    
    sDate = Format(rst("FZDATE"), "000000")
    if right(sdate,2)>"50" then   'or wherever your beakpoint
      icent = 1900
    else
      icent = 2000
    end if
    dDate = DateSerial(iCent+ CInt(Right(sDate, 2)), CInt(Left(sDate, 2)), CInt(Mid(sDate, 3, 2)))
    MsgBox dDate

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[sunmorgus]

Skip,
That would work, however, I need to be able to use a WHERE statement in my sql to filter for a user defined date range (see the sql and code below). The code you provided would convert the string after it has been imported.

My where statement does not seem to work however. I get an error stating:

SQL0401 Comparison operator BETWEEN operands not compatible.
Cause.....: the operands of comparison operator BETWEEN are not compatible etc, etc, etc.

Could this be because my strStartDate and strEndDate are declared as dates? If so, what else could I declare them as?

Dim adFZFUELRs As ADODB.Recordset
Dim intFldCount As Integer
Dim intICol As Integer
Dim strSQL As String
Dim intRow As Integer
Dim MyRange As Range
Dim strStartDate As Date
Dim strEndDate As Date
Dim strSQLDate As String

strSQLDate = "substr(digits(fzdate),1,2)||'/'||substr(digits(fzdate),3,2)||'/'||substr(digits(fzdate),5,2)"

Rows.Delete

strStartDate = InputBox("Enter the Start Date", "Start Date")
strEndDate = InputBox("Enter the End Date", "End Date")

Set adAS400Conn = New ADODB.Connection
adAS400Conn.Open "Provider=IBMDA400.DataSource.1;Persist Security Info=False;Data Source=***.***.*.*"

strSQL = "SELECT FZUNIT as UNIT#, FZSTAT as ST, FZDV# as DIV, " & _
            strSQLDate & " as DATE, FZCK# as INVOICE#, FZAGNT as TRUCK, " & _
            "FZVNDR as STOP, FZCITY as CITY, FZQTY1 as GALLONS, FZAMT1 as TOTAL_COST " & _
            "FROM IESFILE.FZFUEL " & _
            "WHERE " & strSQLDate & " Between " & strStartDate & " And " & strEndDate
Debug.Print strSQL
Set adFZFUELRs = New ADODB.Recordset
adFZFUELRs.Open strSQL, adAS400Conn, adOpenKeyset

' Copy field names to the first row of the worksheet
intFldCount = adFZFUELRs.Fields.Count
For intICol = 1 To intFldCount
    Cells(1, intICol).Value = adFZFUELRs.Fields(intICol - 1).Name
Next

'copy the data into the workbook
intFldCount = adFZFUELRs.RecordCount
Cells(2, 1).CopyFromRecordset adFZFUELRs

'do formula for cost per gallon
Cells(1, 11).Value = "COST PER GALLON"
intRow = Rows.Row + 1
intFldCount = adFZFUELRs.RecordCount
Set MyRange = Range("K2:K" & intFldCount)
MyRange.Formula = "=J" & intRow & "/I" & intRow

'sort the data by location
Cells.Select
Selection.Sort Key1:=Range("G1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

'format the worksheet
Columns.AutoFit
Rows.AutoFit
Range("D:D").NumberFormat = "General"
Range("K:K").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
Range("J:J").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
Cells(2, 1).Select
ActiveWindow.FreezePanes = True
Range("A1:K1").Font.Bold = True

Cells(1, 1).Select
adFZFUELRs.Close
Set adFZFUELRs = Nothing
CloseConn

End Sub

 

[SkipVought]

Hi,

You dont REALLY have dates -- you have STRINGS.

Hence you need tics...

"WHERE " & strSQLDate & " Between '" & strStartDate & "' And '" & strEndDate & "' "

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[sunmorgus]

Thanks Skip! Star for you!

 

[fredericofonseca]

just one apart.

For your sql to work correctly, and as you are NOT using date fields, you need to make sure that input dates, and the output of your substr combination is on format ccyymmdd, otherwise it will NOT give you the desired results.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[sunmorgus]

Frederico...the input dates can be entered as mm/dd/yy, because the original fields in the as400 are integers, and follow a mmddyy format, non standard for as400 from what I have been told.

Ok, one caveat of this whole process, which I noticed just today. If I input the dates 12/20/05 as strStartDate and 01/30/06 as strEndDate then I get an error "Method 'Range' of object '_Global' failed." I only get this error if I cross years...is there some way to resolve this?

 

[PHV]

And which line of code is highlighted when in debug mode ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sunmorgus]

The bolded line...Set MyRange = Range("K2:K" & intFldCount)

Sub GetData()
'On Error GoTo handleerrors
Dim adFZFUELRs As ADODB.Recordset
Dim intFldCount As Integer
Dim intICol As Integer
Dim strSQL As String
Dim intRow As Integer
Dim MyRange As Range
Dim strStartDate As Date
Dim strEndDate As Date
Dim strSQLDate As String
Dim strSubtot1 As String

strSQLDate = "substr(digits(fzdate),1,2)||'/'||substr(digits(fzdate),3,2)||'/'||substr(digits(fzdate),5,2)"

Rows.Delete

strStartDate = InputBox("Enter the Start Date", "Start Date")
strEndDate = InputBox("Enter the End Date", "End Date")

Set adAS400Conn = New ADODB.Connection
adAS400Conn.Open "Provider=IBMDA400.DataSource.1;Password=RAVENS11;Persist Security Info=False;User ID=ITADMIN;Data Source=192.168.1.5"

strSQL = "SELECT FZUNIT as UNIT#, FZSTAT as ST, FZDV# as DIV, " & _
            strSQLDate & " as DATE, FZCK# as INVOICE#, FZAGNT as TRUCK, " & _
            "FZVNDR as STOP, FZCITY as CITY, FZQTY1 as GALLONS, FZAMT1 as TOTAL_COST " & _
            "FROM IESFILE.FZFUEL " & _
            "WHERE " & strSQLDate & " Between '" & strStartDate & "' And '" & strEndDate & "'"
Set adFZFUELRs = New ADODB.Recordset
adFZFUELRs.Open strSQL, adAS400Conn, adOpenKeyset

' Copy field names to the first row of the worksheet
intFldCount = adFZFUELRs.Fields.Count
For intICol = 1 To intFldCount
    Cells(1, intICol).Value = adFZFUELRs.Fields(intICol - 1).Name
Next

'copy the data into the workbook
intFldCount = adFZFUELRs.RecordCount
Cells(2, 1).CopyFromRecordset adFZFUELRs

'do formula for cost per gallon
Cells(1, 11).Value = "COST PER GALLON"
Cells(1, 12).Value = "COST PER STOP"
intRow = Rows.Row + 1
intFldCount = adFZFUELRs.RecordCount
[b]Set MyRange = Range("K2:K" & intFldCount)[/b]
MyRange.Formula = "=J" & intRow & "/I" & intRow

'sort the data by location
Cells.Select
Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Key2:=Range("H1"), Order2:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

'get the subtotals
Cells.Select
Selection.Subtotal groupby:=3, Function:=xlCount, totallist:=10
intFldCount = adFZFUELRs.RecordCount
intRow = Rows.Row + 1
Do Until intRow = adFZFUELRs.RecordCount
    If Len(Cells(intRow, 1).Value) = 0 Then
        Cells(intRow, 7).Value = Cells(intRow, 10).Value
        strSubtot1 = "=Sum(" & Mid(Cells(intRow, 10).Formula, 13)
        Cells(intRow, 10).Formula = strSubtot1
        Cells(intRow, 10).Select
        Selection.Copy
        Range("I" & intRow).Select
        ActiveSheet.Paste
        Cells(intRow, 12).Formula = "=(" & Cells(intRow, 10).Value & ")/(" & Cells(intRow, 7).Value & ")"
        Cells(intRow, 10).Font.Bold = True
        Cells(intRow, 8).Font.Bold = True
        Cells(intRow, 12).Font.Bold = True
        Cells(intRow, 3).Value = Cells(intRow, 3).Value & " / Total Cost / Cost Per Stop"
    End If
    intRow = intRow + 1
Loop

'format the worksheet
Columns.AutoFit
Rows.AutoFit
Range("D:D").NumberFormat = "mm/dd/yyyy"
Range("K:K").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
Range("J:J").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
Range("L:L").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
Range("I:I").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Cells(2, 1).Select
ActiveWindow.FreezePanes = True
Range("A1:L1").Font.Bold = True

Cells(1, 1).Select
adFZFUELRs.Close
Set adFZFUELRs = Nothing
CloseConn

err_exit:
    Exit Sub

handleerrors:
    MsgBox "Either you canceled the process, or there has been an error."
    Resume err_exit
End Sub