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

 

[RoryA]

Just run Sum - it calls the code to create the schema file at the start.

 

[czl103]

I tried, but error happens: [FONT=&quot]Data type mismatch in criteria expression[/FONT]

 

[czl103]

I have updated the code, but there are 2 problems: 1, not show and sort by the date; 2, there are empty in the sum. Pls help to check, thanks in advance.

Pls see the files,

https://jmp.sh/Z1GajMm

 

[RoryA]

It works fine for me.

 

[czl103]

Could u pls help to check? https://jmp.sh/u0TSn9X

 

[RoryA]

Check what exactly?

 

[czl103]

Pls download the files and check why the result is not grouped by date? thanks in advance:
https://jmp.sh/lO5voNx

 

[RoryA]

I looked at the files and it was grouped.

 

[czl103]

Pls see the running result in the link, thanks...
https://jmp.sh/nVWZTGR

 

[RoryA]

There's not much I can say. The code works for me with your first two data sets. (I can't look at the third one as I can't open rar files)