Problem with Dsum (1 Viewer)

basilyos

Registered User.
Local time
Today, 05:58
Joined
Jan 13, 2014
Messages
252
i have a query contains [ID] and [Amount]

i wrote in vba

Diff:
Dim BulkTotal as integer

BulkTotal = DSum("Account_Amount", "[qry_Bulk_Total]")

am getting this error

Runtime error 6
Overflow

any help?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:58
Joined
Oct 29, 2018
Messages
21,358
Try:
Code:
Dim BulkTotal As Currency
 

plog

Banishment Pending
Local time
Today, 07:58
Joined
May 11, 2011
Messages
11,613
To paraphrase my grandfather (he would never use the term "manure")--you can't shove 10lbs of manure in a 5lb sack.


An integer just isn't big enough to hold the number you are trying to put into it, so use a bigger number type like theDbguy suggests.

Also, I don't think it matters but you're brackets should be put on the field name and not the query name:

BulkTotal = DSum("[Account_Amount]", "qry_Bulk_Total")
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:58
Joined
Feb 19, 2002
Messages
42,981
Bracket names are only necessary when the field/object name is improperly constructred.

Access does get "helpful" in the QBE so do be careful there. If it thinks you can enter a text string, it will frequently assume a field name is a string but in VBA, Access doesn't make that "helpful" assumption.
 

Cotswold

Active member
Local time
Today, 12:58
Joined
Dec 31, 2020
Messages
521
Your Integer will overflow outside the range -32,768 to +32,767
A Long Integer's range is -2,147,483,648 to +2,147,483,647
If the decimal section isn't necessary with the DSum() rounded, then Long should be Ok.......Unless of course you are doing something for Amazon

As the theDBguy said a Currency will solve your problem.
Or a use Single which gives +-3.402823E38 with 6 digit precision. Or a Double +-1.79769313486232E308 with 10 digit precision.

I tend to use Long Integers unless I know an Integer will definitely be Ok. There is a small saving with Integer over Long Integer, but in most cases space is unimportant.
 

Isaac

Lifelong Learner
Local time
Today, 05:58
Joined
Mar 14, 2017
Messages
8,738
I tend to use Long Integers unless I know an Integer will definitely be Ok. There is a small saving with Integer over Long Integer, but in most cases space is unimportant.
+1 for this statement.

I stopped stopping to scratch my head over variable typing choices like Byte, Int, etc., a long time ago--unless there was a rare and specific reason I wanted to test if something could be converted to such--and just use Long almost "all the time" (for whole numbers). Simplifies life. Especially when I learned that VBA treats several types as if they were Longs behind the scenes anyway..

May not apply to OP's money situation, of course, but possibly helpful to someone learning to make numerical type choices generally speaking.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:58
Joined
Feb 19, 2002
Messages
42,981
Accurate data typing is significantly more important when you have lots of data to store and process. You want to avoid using too much space by settling on large data types and you want to avoid internal conversions because they slow things down.
 

Isaac

Lifelong Learner
Local time
Today, 05:58
Joined
Mar 14, 2017
Messages
8,738
Accurate data typing is significantly more important when you have lots of data to store and process. You want to avoid using too much space by settling on large data types and you want to avoid internal conversions because they slow things down.
In the context of SQL column data types, I would agree that it may become significant in the context of substantial volume. Int vs. Long typing of a VBA variable....maybe not so much if at all, just my opinion, since, of course "much" or "little" are subjective terms.

This article is interesting, though:
The Integer Data Types | Microsoft Docs

Traditionally, VBA programmers have used integers to hold small numbers, because they required less memory. In recent versions, however, VBA converts all integer values to type Long, even if they're declared as type Integer. So there's no longer a performance advantage to using Integer variables; in fact, Long variables may be slightly faster because VBA does not have to convert them.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:58
Joined
Feb 19, 2002
Messages
42,981
2 bytes vs 4. Seems to me one is double the size of the other. Not only do you have to store those extra two bytes for every row, you have to move them around in every query. Up to the server and back across the network REGARDLESS of what you are using as a data store. For most people, there won't be an issue. But sloppy is as sloppy does. When you know what is right, it is always best to make it a habit to do it right the first time rather than make excuses for not doing it right.

The article you referenced is over 20 years old. It may or may not still be valid. BTW, conversions only happy when you are doing arithmetic and I rarely do arithmetic with integers. I even keep quantity fields as non-integers just in case we get an item that might allow fractional values.
 

Users who are viewing this thread

Top Bottom