Problem with SQL in VBA

czl103

New Member
Joined
Sep 27, 2018
Messages
19
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....
Code:
[URL="https://www.mrexcel.com/forum/redirect-to/?redirect=https%3A%2F%2Fjmp.sh%2F71yC8Ti"]https://jmp.sh/71yC8Ti[/URL]

Thanks in advance.

PHP:
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 & ")"
 
Last edited by a moderator:
Just run Sum - it calls the code to create the schema file at the start.
 
Upvote 0

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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,
Code:
https://jmp.sh/Z1GajMm
 
Upvote 0
It works fine for me.
 
Upvote 0
I looked at the files and it was grouped.
 
Upvote 0
nVWZTGR
nVWZTGR
nVWZTGR
Pls see the running result in the link, thanks...
https://jmp.sh/nVWZTGR
 
Last edited:
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top