Error: overflow.

[Homegrownandy]

Ive been looking into what ths error is but im failing to see a work around.

When I enter a value over around 33000 (I've read online that is was 60k but for some reason it freaks out around 33k for me.) I may be dealing with numbers much greater than this.

I'm just looking for suggestions of things I can look into to try or alternatives.

Clarification: the error occurs when using SUM. I'm using Dsum in VBA which is triggering. This may not be a programming issue/solution.

thanks, Andy

[JoeM]

It sounds like you may have declared a number as an Integer. The maximum integer value is 32,767.
Try picking a different data type, like Long Integer.
See: https://eggerapps.at/mdbviewer/docs/en/field-types.html

If this appears in a SUM, make sure that the underlying values you are summing are not declared as Integers.

[Homegrownandy]

I appreciate your response. I will spend tomorrow morning investigating it further.

Andy.

[Homegrownandy]

I had a little look before I go home today. I have implemented your suggestion to the VBA and it works perfectly. BUT, there is still a problem in the backend. It saying the expression is too complex. I have been using this for quite some time with no issue. It's only a problem with large numbers. The fields in question are Quantity and Price per unit. Quantity is already long integer and Price per unit is currency. I can look into changing this field from currency to long integer and see what implications this has. Unless there are other ideas?

Thanks.

[JoeM]

Note that Integers and Long Integers will drop decimals, so you may want to try "Double" instead.
Also note, an error denoting an "expression is to complex" may be referring to your calculation, not your table design.

[Minty]

Just to add to Joe's comments Currency is an excellent choice for most decimal fields, as it is effectively a decimal value of upto 15 digits plus 4 decimal places.

Single and especially Double can suffer from rounding errors in complicated expressions over lots of data.

[Homegrownandy]

I’ve changed it to Large number, I don’t see any option for 'double'. With Large Number it still returns a ”#Num!" but I was using numbers much larger than we would ever normally use (for testing purposes). Having now changed the numbers to return something more reasonable it is working fine.

I'll test this using multiple rows on one report shortly but if I encounter errors, I should be able to fix them based on the advice here. Appreciate the help/replies.

Thanks.