VBA sum problem

[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? Many thanks for help.

Dim cnn As Object, SQL$, s$, p$, f$
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()
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 & ")"

 

[joeu2004]

Just a guess, since I'm not familiar with SQL, much less importing data using SQL.

I wonder if the format of column B (or at least B2:Bx, x = R) is Number with zero decimal places, and the option "Precision as displayed" is selected.

For the latter, click File > Options > Advanced, and scroll down to the option near the end.

That would round decimal fractions, not "ignore" them (truncate). But perhaps enough of them are rounded down to explain the discrepancy.

 

[czl103]

Just a guess, since I'm not familiar with SQL, much less importing data using SQL.

I wonder if the format of column B (or at least B2:Bx, x = R) is Number with zero decimal places, and the option "Precision as displayed" is selected.

For the latter, click File > Options > Advanced, and scroll down to the option near the end.

That would round decimal fractions, not "ignore" them (truncate). But perhaps enough of them are rounded down to explain the discrepancy.

Thanks, but I've checked the option, the box before ""Precision as displayed" is not ticked...Maybe there is another reason...

 

[joeu2004]

Again, I know nothing about SQL. But hopefully your problem has nothing to do with that.

I would need to see the Excel file after executing the SQL import procedure.

Upload an example Excel file (redacted) that demonstrates the problem after the SQL import to a file-sharing website (e.g. box.net/files), and post the public/share URL in a response here. Test the download URL first, being careful to log out of the file-sharing website. (If you use box.net/files, ignore any preview errors, and just download.)

Caveat: Some participants object because they cannot or will not download files. But in this case, the devil might be in the details that we can see only in the Excel file.

 

[czl103]

Pls see and download the excel file:

http://note.youdao.com/noteshare?id=54496f52cb042a3463bef0f12f356bf1&sub=3FCA46087BB44DCBA2A261EF2D0ABFDB

 

[joeu2004]

Since we're speaking English in this forum, it should come as no surprise that I do not read Chinese. So I do not know how to download the file. I tried clicking and right-clicking the file name and file icon, but neither worked.

In any case, it would be better if you upload just an Excel file (".xls", ".xlsx" or ".xlsm"), not a ".zip" file, unless you really need to provide multiple files. I hope you can avoid that, since I only asked for an example Excel file after the SQL import.

So please use an English file-sharing website. And please upload just an Excel file after executing the VBA procedure in your original posting.

 

[czl103]

pls download the 2 files from below site, and run the xlsm file after downloading....

https://jmp.sh/71yC8Ti

 

[Baziwan]

Try adding this

Dim Sum as String

might work

 

[joeu2004]

@czl.... I believe the problem is with the SQL operation, not with VBA or Excel.

I suggest that either you repost your question in a database (MS Access) forum, or at least you re-title this thread, if you can, to something like "Problem with SQL in VBA" in order to attract another pair of eyes, someone who might know SQL.

-----

Details....

Thank you for providing both the Excel file and original data in the CSV file.

I was not able to execute the VBA procedure; I cannot get past the cnn.open statement because the "Provider cannot be found".

However, I was able to look at the CSV file.

In H2016:H2037 of the CSV file, we find numbers that do sum to 321.3, as you expect.

The sum of the rounded numbers, =SUMPRODUCT(ROUND(H2016:H2037,0)), is 323.

But the sum of the truncated numbers, =SUMPRODUCT(INT(H2016:H2037)), is 310, the value that your VBA procedure calculates in B3 of the Excel file.

Note that both VBA and Excel would sum the rounded numbers, not the truncated numbers.

So I conclude that the source of the truncated numbers is the SQL statement.

Actually, I believe the SQL statement is the source of the sum of the truncated numbers -- perhaps "subtotal(Amount)".

I do not believe the sum is calculated by VBA or Excel.

Note that in the Excel worksheet, we see only one line of data in A2:B2, and B2 contains the constant 310.

And we see only =SUM(B2:B2) in B3. That formula is created by the VBA procedure.

The single value 310 in B2 is created by cnn.Execute(SQL), which is stored into A2:B2 by the VBA procedure.

I think you are expecting a column of data: at least 22 rows (H2016:H2037 in the CSV file), if not the entire column of data from the CSV file.

-----

Please note the following comments, which are not related to your problem....


1. Change Dim R% to Dim R&. Actually, I would prefer Dim R As Long.

R% is type Integer. It supports only up to 32767 rows.

Even if that might be sufficient for your purposes, it is "good practice" to use type Long for row numbers, since the Excel file supports up to 1+ million rows.

R& is type Long.

I think it is better to use "As String" and "As Long" instead of the implicit type characters "$" and "&".


2. I think the following is more flexible:

Change [A2:E1048576] = "" to Range("a2:e" & Rows.Count) = ""

Change [A1048576].End(3).Row to Cells(Rows.Count,"a").End(xlUp).Row

Frankly, I am surprised that End(3) works. The value of xlUp is -4162.

 

[joeu2004]

Clarification....

Note that both VBA and Excel would sum the rounded numbers, not the truncated numbers.

What I mean is: if we assume that VBA and/or Excel is summing integers, not the actual numbers with decimal fractions, VBA and Excel would round, not truncate, unless we cause truncation explicitly.

But we would not expect VBA and Excel to sum only the integer value, in the first place. Something that we do must cause that to happen.

For Excel, I had mention the "Precision as displayed" option. You verified the option is not set.

For VBA, we might assign the original numbers to an integer variable (type Integer or type Long). I do not see that in your VBA code.

And again, in either case, those implicit conversions would round, not truncate.