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.

Code:
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:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,145
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Just run Sum - it calls the code to create the schema file at the start.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

czl103

New Member
Joined
Sep 27, 2018
Messages
19
I tried, but error happens: [FONT=&quot]Data type mismatch in criteria expression[/FONT]
 

czl103

New Member
Joined
Sep 27, 2018
Messages
19
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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,145
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It works fine for me.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,145
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Check what exactly?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,145
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I looked at the files and it was grouped.
 

czl103

New Member
Joined
Sep 27, 2018
Messages
19
nVWZTGR
nVWZTGR
nVWZTGR
Pls see the running result in the link, thanks...
https://jmp.sh/nVWZTGR
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,145
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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)
 

Forum statistics

Threads
1,136,792
Messages
5,677,757
Members
419,718
Latest member
ALWP

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
Top