Problem with Dsum

[basilyos]

i have a query contains [ID] and [Amount]

i wrote in vba

Dim BulkTotal as integer

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

am getting this error

Runtime error 6
Overflow

any help?

 

[theDBguy]

Try:

Dim BulkTotal As Currency

 

[plog]

To paraphrase my grandfather (he would never use the term "manure")--you can't shove 10lbs of manure in a 5lb sack.

Overflow (Error 6)

Office VBA reference topic

docs.microsoft.com

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]

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.

 

[basilyos]

Try:

Dim BulkTotal As Currency

thanks man

 

[Cotswold]

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.

 

[theDBguy]

thanks man

You're welcome. We're all happy to assist. Good luck with your project.

 

[Isaac]

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]

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]

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.

 

[Pat Hartman]

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.