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")

 

[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.

 

[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.