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!

 

[sunmorgus]

commenting that line and everything below, I ran the sheet again, and I don't get any data for that date range. I know data exsists within the range however...

 

[PHV]

Set MyRange = Range("K2:K" & (2 + intFldCount))

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

 

[sunmorgus]

Well, that would work, but I still am not getting any data when I should be...why would the sql not return any data?

 

[PHV]

What is the value of strSQL ?
Tip: play with MsgBox or Debug.Print

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

 

[sunmorgus]

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 & "'"

 

[PHV]

I asked for the value (at run time), not the code you've already posted.

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

 

[SkipVought]

What you just posted is NOT the value of strSQL. You posted the CODE.

Please post the VALUE that is in strSQL at execution time, using a MsgBox or Debug.Print.


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

 

[sunmorgus]

sorry about that, it is getting late in the day for me i suppose...

SELECT FZUNIT as UNIT#, FZSTAT as ST, FZDV# as DIV, substr(digits(fzdate),1,2)||'/'||substr(digits(fzdate),3,2)||'/'||substr(digits(fzdate),5,2) 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 substr(digits(fzdate),1,2)||'/'||substr(digits(fzdate),3,2)||'/'||substr(digits(fzdate),5,2) Between '12/20/2005' And '01/30/2006'

 

[PHV]

Between '12/20/2005' And '01/30/2006'
You're comparing strings and '12/20/2005' is GREATER than '01/30/2006' !
Either use REAL date values or strings formatted as yyyymmdd for correct lexicographic sort.

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

 

[PHV]

A starting point:
WHERE substr(digits(fzdate),5,2)||substr(digits(fzdate),1,2)||substr(digits(fzdate),3,2) Between '20051220' And '20060130'

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

 

[SkipVought]

and...

you also inconsistently used a 2-DIGIT YEAR in

substr(digits(fzdate),1,2)||'/'||substr(digits(fzdate),3,2)||'/'||substr(digits(fzdate),5,[red][b]2)[/b][/red]

while your BETWEEN strings had 4-digits years.

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

 

[PHV]

Good catch Skip !

 

[fredericofonseca]

I was going to comment on

[/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. ]

but you guys did it for me.

But again ...

quoting myself.

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]

Ok, sorry for the skepticism, but I see your point, and finally understand. The only thing I am worried about is that the original dates on the 400 do not have 4 digit years, but 2 digit years. Also, let me see if I understand correctly...in order for this to work, my user would have to enter a date in the yyyymmdd format, or I would have to convert the string after he/she was completed, correct? That I assume would be easy, but would I then also have to convert the dates on the 400 to the same format, and if so, how would I do so when I only have a 2 digit year on the 400?

By the way, thank you guys so much for the help. Like I said before, this is my first time dealing with a connection to an as400, and working with vba in excel. I am sorry if I have seemed a bit stubborn throughout all of this, and assure you it was not intentional...

 

[sunmorgus]

PHV...I will try your example on monday when I return to work, and see what my output is...I will convert the user input to a similar string before putting into the sql, and will post the outcome...

 

[fredericofonseca]

First do NEVER use reserved words as field names e.g. DATE

Assuming a date in format yymmdd

case
when substr(digits(fzdate),1,2) < '00' then
'19'||substr(digits(fzdate),1,2)||'/'||substr(digits(fzdate),3,2)||'/'||substr(digits(fzdate),5,2)
when substr(digits(fzdate),1,2) >= '00' then'20'||substr(digits(fzdate),1,2)||'/'||substr(digits(fzdate),3,2)||'/'||substr(digits(fzdate),5,2)
end as fzdate


Not tested but should be something similar to the above.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[sunmorgus]

I will try that myself, but should note that I have had trouble with using a case statement in my sql before. For some reason, only the first when statement would be read by the server. At the time however I was trying to use a len statement, and actually, now that I think about it, I can see where a len statement would be wrong, because the field is set to a lenght of 6, so when I was checking for a length of 5 I would imagine it would not be able to see that...is that correct?

I will change the output field name as well...should have know that myself. Thanks for all of the help!

 

[sunmorgus]

Ok, really starting to get frustrated now...here is my sql output...

SELECT FZUNIT as UNIT#, FZSTAT as ST, FZDV# as DIV, '20'||substr(digits(fzdate),5,2)||'/'||substr(digits(fzdate),1,2)||'/'||substr(digits(fzdate),3,2) as FROM_DATE, FZCK# as INVOICE#, FZAGNT as TRUCK, FZVNDR as STOP, FZCITY as CITY, FZQTY1 as GALLONS, FZAMT1 as TOTAL_COST FROM IESFILE.FZFUEL WHERE '20'||substr(digits(fzdate),5,2)||'/'||substr(digits(fzdate),1,2)||'/'||substr(digits(fzdate),3,2) Between  '20051201' And '20051230'

I tried Frederico's case statement as well, but it didn't work, so I tried just putting the '20' in front, because the table does not have and never will have data from the 1900's, but I still get the same "Method 'Range' of object '_Global' failed." Below is a picture of the FZDATE field from the as400, so you can see there are dates in december. I even tried inputing 01/01/2006 to 01/30/2006 and get the error, so now it doesn't work at all! GRRRR!!!!!

 

[PHV]

You may try something like this:
SELECT FZUNIT as UNIT#, FZSTAT as ST, FZDV# as DIV
, CASE WHEN LENGTH(FZDATE)=6 THEN '20'||substr(digits(fzdate),5,2)||substr(digits(fzdate),1,4)
ELSE '20'||substr(digits(fzdate),4,2)||'0'||substr(digits(fzdate),1,3) END AS FROM_DATE
, FZCK# as INVOICE#, FZAGNT as TRUCK, FZVNDR as STOP, FZCITY as CITY, FZQTY1 as GALLONS, FZAMT1 as TOTAL_COST
FROM IESFILE.FZFUEL
WHERE CASE WHEN LENGTH(FZDATE)=6 THEN '20'||substr(digits(fzdate),5,2)||substr(digits(fzdate),1,4)
ELSE '20'||substr(digits(fzdate),4,2)||'0'||substr(digits(fzdate),1,3) END Between '20051201' And '20051230'

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

 

[sunmorgus]

Ok, I figured it out...when I converted the user inputed dates, I forgot to put the "/"'s in..hehe! Here is the completed code if anyone is interested...

Option Explicit
Dim adAS400Conn As ADODB.Connection


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 String
Dim strEndDate As String
Dim dtStartDate As Date
Dim dtEndDate As Date
Dim strSQLDate As String
Dim strSQLDate2000 As String
Dim strsqldate1900 As String
Dim strSubtot1 As String

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

Rows.Delete

dtStartDate = InputBox("Enter the Start Date", "Start Date")
dtEndDate = InputBox("Enter the End Date", "End Date")

'convert the inputed dates to string, and format for the search
strStartDate = Mid(dtStartDate, 7, 4) & "/" & Mid(dtStartDate, 1, 2) & "/" & Mid(dtStartDate, 4, 2)
strEndDate = Mid(dtEndDate, 7, 4) & "/" & Mid(dtEndDate, 1, 2) & "/" & Mid(dtEndDate, 4, 2)

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, " & _
            strSQLDate2000 & " as FROM_DATE, FZCK# as INVOICE#, FZAGNT as TRUCK, " & _
            "FZVNDR as STOP, FZCITY as CITY, FZQTY1 as GALLONS, FZAMT1 as TOTAL_COST " & _
            "FROM IESFILE.FZFUEL " & _
            "WHERE " & strSQLDate2000 & " 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"
Cells(1, 12).Value = "COST PER STOP"
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("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
Public Sub CloseConn()
adAS400Conn.Close
Set adAS400Conn = Nothing
End Sub