Problem with SQL in VBA

[czl103]

After below code runs, the result is 310, however the result of VBA code should be 321.3, all the digits after the decimal points were ignored in calculation. why does this happens?
Pls download the 2 files from below site, and run the xlsm file after downloading....

[URL="https://www.mrexcel.com/forum/redirect-to/?redirect=https%3A%2F%2Fjmp.sh%2F71yC8Ti"]https://jmp.sh/71yC8Ti[/URL]

Thanks in advance.

Dim cnn As Object, SQL As String, s As String, p As String, f As String
Set cnn = CreateObject("ADODB.Connection")
p = ThisWorkbook.Path & "\"f = Dir(p & "*.CSV")
cnn.Open "Provider=Microsoft.ace.OLEDB.16.0;Extended Properties='text;FMT=Delimited(,);hdr=YES';Data Source=" & p
Do While f <> ""   SQL = SQL & " SELECT int(Date) AS T, Amount 
FROM [" & f & "] 
UNION ALL "    f = Dir()LoopSQL = Left(SQL, Len(SQL) - 11)
SQL = "SELECT T,SUM(Amount) FROM (" & SQL & ") 
GROUP BY T"Range("a2:e" & Rows.Count) = ""[a2].CopyFromRecordset 
cnn.Execute(SQL)
cnn.CloseSet 
cnn = Nothing
Dim R%R = Cells(Rows.Count, "a").End(xlUp).Row[a1].Offset(R, 0) = "Sum"[b1].Offset(R, 0).Resize(1, 4) = "=sum(b2:b" & R & ")"

 

[czl103]

pls help, masters....

 

[czl103]

Waiting for masters...

 

[czl103]

Waiting for masters...

 

[czl103]

Now I found one method to solve this problem, change the format of the first row number to "0.00", then the result is ok.
But this is not clever way, is there any good idea?

 

[strooman]

I got an error on the line

[a2].CopyFromRecordset cnn.Execute(SQL)

undefined function "subtotal" in expression


Where does subtotal(Amount) refer to?
The column Amount in your csv file is empty when I import the csv file. Is this correct?
When I import your csv data I get this. Is this the correct data?

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Order No	Date	Customer 1 No	Customer 1 Name	Customer 2 No	Customer 2 Name	Amount
2​	1,11E+02​	11-1-2017 21:00​	2,22E+02​	aaa	3,33E+02​	bbb	-
3​	1,11E+02​	11-1-2017 20:00​	2,22E+02​	aaa	3,33E+02​	bbb	-
4​	1,11E+02​	11-1-2017 20:00​	2,22E+02​	aaa	3,33E+02​	bbb	-
5​	1,11E+02​	11-1-2017 20:00​	2,22E+02​	aaa	3,33E+02​	bbb	-
6​	1,11E+02​	11-1-2017 20:00​	2,22E+02​	aaa	3,33E+02​	bbb	-
7​	1,11E+02​	11-1-2017 20:00​	2,22E+02​	aaa	3,33E+02​	bbb	-
8​	1,11E+02​	11-1-2017 20:00​	2,22E+02​	aaa	3,33E+02​	bbb	-
9​	1,11E+02​	11-1-2017 20:00​	2,22E+02​	aaa	3,33E+02​	bbb	-

 

[RoryA]

You can use a Schema.ini file to specify data types for the columns, like this:

Sub Sum()
    Dim cnn As Object, SQL$, s$, p$, f$
    Set cnn = CreateObject("ADODB.Connection")
    p = ThisWorkbook.Path & "\"
    CreateSchemaFile p
    f = Dir(p & "*.CSV")
    cnn.Open "Provider=Microsoft.ace.OLEDB.12.0;Extended Properties='text;';Data Source=" & p

    Do While f <> ""
        SQL = SQL & " SELECT int(Date) AS T, Amount FROM [" & f & "] UNION ALL "
        f = Dir()
    Loop
    SQL = Left(SQL, Len(SQL) - 11)
    SQL = "SELECT T,sum(Amount) FROM (" & SQL & ") GROUP BY T"
    [A2:E1048576] = ""
    [a2].CopyFromRecordset cnn.Execute(SQL)
    cnn.Close
    Set cnn = Nothing
    Dim R%
    R = [A1048576].End(3).Row
    [a1].Offset(R, 0) = "Sum"
    [b1].Offset(R, 0).Resize(1, 4) = "=sum(b2:b" & R & ")"
End Sub
Sub CreateSchemaFile(FilePath As String)
    If Right$(FilePath, 1) <> "\" Then FilePath = FilePath & "\"

    Dim IntFileHandleLog As Integer
    IntFileHandleLog = FreeFile
    
    On Error Resume Next
    Kill FilePath
    On Error GoTo 0
    
    Open FilePath & "schema.ini" For Output As #IntFileHandleLog
    
    Dim FileName As String
    FileName = Dir(FilePath & "*.csv")
    
    If FileName <> vbNullString Then
        Do
            Print #IntFileHandleLog, "[" & FileName & "]"
            Print #IntFileHandleLog, "ColNameHeader=True"
            Print #IntFileHandleLog, "MaxScanRows = 0"
            Print #IntFileHandleLog, "Col1=" & """Order No"" Text"
            Print #IntFileHandleLog, "Col2=" & """Date"" DateTime"
            Print #IntFileHandleLog, "Col3=" & """Customer 1 No"" Text"
            Print #IntFileHandleLog, "Col4=" & """Customer 1 Name"" Text"
            Print #IntFileHandleLog, "Col5=" & """Customer 2 No"" Text"
            Print #IntFileHandleLog, "Col6=" & """Customer 2 Name"" Text"
            Print #IntFileHandleLog, "Col7=" & """Amount"" Currency"
            FileName = Dir
        Loop While FileName <> vbNullString
    End If
    Close #IntFileHandleLog
End Sub

 

[czl103]

Pls refer to response with red text

Where does subtotal(Amount) refer to?
the G column

The column Amount in your csv file is empty when I import the csv file. Is this correct?
Yes，correct, some non-zero number do exist below these rows

When I import your csv data I get this. Is this the correct data?
yes correct data

Thanks

 

[czl103]

It does work, many thanks...

 

[czl103]

How to run? still run the "Sum"， or should run the Schema.ini file first and then "Sum"?