Problem with Overflow error message in Access

**FishVal** · Sep 19 '08, 08:33 PM

Hello.

Before posting the whole query in all its glory you might localize error source yourself.

Run the query itself. Does it produce the same error?
Sequentially one-by-one remove from the query fetched fields (starting from calculated ones).
If the datasets join with only one non-calculated field (try different ;)) produce the same error, then try to rebuild the query joining datasets one-by-one.

Kind regards,
Fish

**popo725** · Sep 19 '08, 09:05 PM

Originally posted by FishVal

Hello.

Before posting the whole query in all its glory you might localize error source yourself.

Run the query itself. Does it produce the same error?
Sequentially one-by-one remove from the query fetched fields (starting from calculated ones).
If the datasets join with only one non-calculated field (try different ;)) produce the same error, then try to rebuild the query joining datasets one-by-one.

Kind regards,
Fish

Thanks Fish. I am a newbie to Access and the biggest challenge is I am not the creator of this database and therefore, don't have a clue what these syntax expressions mean. Per your instruction, can you provide some pointers using the query I copied in the enquiry to start off the self-checking process?

Many thanks.

**missinglinq** · Sep 19 '08, 09:48 PM

Runtime Error 6: Overflow is caused when you try to cram 10 # of potatoes into a 5 # bag!

One or more of your calculations are resulting in an answer being stored in a field that is too small to hold it. An example would be field, let's call it MyNumber, whose Fieldsize is defined as an Integer. An Integer can hold values up to 32,767. If you were to make an assignment such as

MyNumber = 20000 * 5

it would cause an Overflow error, because 20000 * 5 = 100000 and that result is too large for MyNumber to hold it. The answer, of course, is to change the Fieldsize to one that can hold it, such as a Long Integer, which can hold values up to 2,147,483,647.

When you see a thread where the poster says, "This has worked without a problem for months and now it suddenly starts giving an Overflow error," the problem usually involves something such as a "year-to-date total" field. The calculations run, without problems, early in the year because the total doesn't exceed the Fieldsize limit, but as the "year-to-date total" grows it eventually outstrips the limit and the error pops.

Look at your Fieldsizes in your tables and figure out which totals being assigned into fields that are too small.

Welcome to Bytes!

Linq ;0)>

**popo725** · Sep 22 '08, 01:05 PM

Thanks for this - I have been checking a number of blogs on resolving this issue and would like ask to make such conversion, do I need to put 'CLng' in front of the syntax expression that calculates the YTD number?

Many thanks.

Originally posted by missinglinq

Runtime Error 6: Overflow is caused when you try to cram 10 # of potatoes into a 5 # bag!

One or more of your calculations are resulting in an answer being stored in a field that is too small to hold it. An example would be field, let's call it MyNumber, whose Fieldsize is defined as an Integer. An Integer can hold values up to 32,767. If you were to make an assignment such as

MyNumber = 20000 * 5

it would cause an Overflow error, because 20000 * 5 = 100000 and that result is too large for MyNumber to hold it. The answer, of course, is to change the Fieldsize to one that can hold it, such as a Long Integer, which can hold values up to 2,147,483,647.

When you see a thread where the poster says, "This has worked without a problem for months and now it suddenly starts giving an Overflow error," the problem usually involves something such as a "year-to-date total" field. The calculations run, without problems, early in the year because the total doesn't exceed the Fieldsize limit, but as the "year-to-date total" grows it eventually outstrips the limit and the error pops.

Look at your Fieldsizes in your tables and figure out which totals being assigned into fields that are too small.

Welcome to Bytes!

Linq ;0)>

**popo725** · Sep 22 '08, 04:59 PM

I finally figured out the way to change the 'fieldsize' but confirmed that the existing setting for 'number' is already preset as 'Long Integer'. Any suggestions of what to look for next?

**FishVal** · Sep 22 '08, 05:21 PM

Originally posted by popo725

I finally figured out the way to change the 'fieldsize' but confirmed that the existing setting for 'number' is already preset as 'Long Integer'. Any suggestions of what to look for next?

You should better first localize error source. Otherwise you will seek a black cat in a dark room.
A good start is to open the query in design view and start unchecking query fields one-by-one and running those simplified variants.
Another good start is to run one-by-one all those source queries - one or more could be well a source of error.

Regards,
Fish

**popo725** · Sep 23 '08, 05:46 PM

Thanks for the pointers - will test it out. =)

Problem with Overflow error message in Access

Problem with Overflow error message in Access

Comment

Comment

Comment

Comment

Comment

Comment

Comment