Problem with Overflow error message in Access

[popo725]

Hi,

I'm new at using Microsoft Access (2003), and I've created a "MonthlyReport" query, which has functioned properly in the past and I update the table monthly as I update the data in the underlying table and query files. All the query does is to run some calculations from data inputed every month, and it aggregates the results of those calculations in the report.

I tried to run or display the report under both 'Layout' and 'Print' Preview and I get the error message "Overflow". I'm not sure how to fix the problem. I've searched the forum for answers, but it couldn't find a close-match to guide me how to troubleshoot this problem.

I would greatly appreciate some pointers on how to address this problem.

Here is the SQL I copied from my file.

SELECT [2008Net_qry].GroupCode, PeerComp_tbl.GroupName, [2008Net_qry].FundID, IIf(PeerSet_tbl!PeerSet Is Not Null,"*","") AS PR, PeerComp_tbl.Type, PeerSet_tbl.PeerSet, IIf([2008Net_qry]!GroupCode="AGF","AGF","") AS Flag, [2008Net_qry].FundName, [2008Net_qry].IFSCType, [2008Net_qry].NetAssets, [2008Net_qry].Asset_Rank, [2008Net_qry]![2008_08_tbl.NewMo] AS CrntNet, IIf(IsNull([2008Net_qry]![2008_08_tbl.NewMo]),0,1) AS CrntNetFlag, [2008Net_qry]![2008_08_tbl.Net_Rank] AS CrntNetRnk, [2008Net_qry]![2008_07_tbl.NewMo] AS LstMNet, IIf(IsNull([2008Net_qry]![2008_07_tbl.NewMo]),0,1) AS LstMNetFlag, [2008Net_qry]![2008_07_tbl.Net_Rank] AS LstMNetRnk, [2008Net_qry]![2008_06_tbl.NewMo] AS 3mAgoNet, IIf(IsNull([2008Net_qry]![2008_06_tbl.NewMo]),0,1) AS 3mAgoNetFlag, nz([2008Net_qry]![2008_08_tbl.NewMo])+nz([2008Net_qry]![2008_07_tbl.NewMo])+nz([2008Net_qry]![2008_06_tbl.NewMo])+nz([2008Net_qry]![2008_05_tbl.NewMo])+nz([2008Net_qry]![2008_04_tbl.NewMo])+nz([2008Net_qry]![2008_03_tbl.NewMo])+nz([2008Net_qry]![2008_02_tbl.NewMo])+nz([2008Net_qry]![2008_01_tbl.NewMo])+0 AS CrntNetYTD, nz([2007Net_qry]![2007_08_tbl.NewMo])+nz([2007Net_qry]![2007_07_tbl.NewMo])+nz([2007Net_qry]![2007_06_tbl.NewMo])+nz([2007Net_qry]![2007_05_tbl.NewMo])+nz([2007Net_qry]![2007_04_tbl.NewMo])+nz([2007Net_qry]![2007_03_tbl.NewMo])+nz([2007Net_qry]![2007_02_tbl.NewMo])+nz([2007Net_qry]![2007_01_tbl.NewMo])+0 AS LstNetYTD, [2007Net_qry]![Net 2007] AS LstNetTtl, [2007Net_qry]!NetRnkCat AS LstNetTtlRnk, [2008Gross_qry]![2008_08_tbl.GrossSales] AS CrntGrs, IIf(IsNull([2008Gross_qry]![2008_08_tbl.GrossSales]),0,1) AS CrntGrsFlag, [2008Gross_qry]![2008_08_tbl.Gross_Rank] AS CrntGrsRnk, [2008Gross_qry]![2008_07_tbl.GrossSales] AS LstMGrs, IIf(IsNull([2008Gross_qry]![2008_07_tbl.GrossSales]),0,1) AS LstMGrsFlag, [2008Gross_qry]![2008_07_tbl.Gross_Rank] AS LstMGrsRnk, [2008Gross_qry]![2008_06_tbl.GrossSales] AS 3mAgoGrs, IIf(IsNull([2008Gross_qry]![2008_06_tbl.GrossSales]),0,1) AS 3mAgoGrsFlag, nz([2008Gross_qry]![2008_08_tbl.GrossSales])+nz([2008Gross_qry]![2008_07_tbl.GrossSales])+nz([2008Gross_qry]![2008_06_tbl.GrossSales])+nz([2008Gross_qry]![2008_05_tbl.GrossSales])+nz([2008Gross_qry]![2008_04_tbl.GrossSales])+nz([2008Gross_qry]![2008_03_tbl.GrossSales])+nz([2008Gross_qry]![2008_02_tbl.GrossSales])+nz([2008Gross_qry]![2008_01_tbl.GrossSales])+0 AS CrntGrsYTD, nz([2007Gross_qry]![2007_08_tbl.GrossSales])+nz([2007Gross_qry]![2007_07_tbl.GrossSales])+nz([2007Gross_qry]![2007_06_tbl.GrossSales])+nz([2007Gross_qry]![2007_05_tbl.GrossSales])+nz([2007Gross_qry]![2007_04_tbl.GrossSales])+nz([2007Gross_qry]![2007_03_tbl.GrossSales])+nz([2007Gross_qry]![2007_02_tbl.GrossSales])+nz([2007Gross_qry]![2007_01_tbl.GrossSales])+0 AS LstGrsYTD, [2007Gross_qry]![Gross 2007] AS LstGrsTtl, [2007Gross_qry]!GrsRnkCat AS LstGrsTtlRnk, [2006Net_qry]![Net 2006] AS 3yrAgoNetTtl, [2006Gross_qry]![Gross 2006] AS 3yrAgoGrsTtl, [2007Net_qry]![2007_08_tbl.NewMo] AS YrAgoNet, [2007Gross_qry]![2007_08_tbl.GrossSales] AS YrAgoGrs
FROM ((((((2008Net_qry LEFT JOIN 2006Net_qry ON [2008Net_qry].FundID = [2006Net_qry].FundID) LEFT JOIN 2007Net_qry ON [2008Net_qry].FundID = [2007Net_qry].FundID) LEFT JOIN 2007Gross_qry ON [2008Net_qry].FundID = [2007Gross_qry].FundID) LEFT JOIN PeerSet_tbl ON [2008Net_qry].FundID = PeerSet_tbl.FundID) LEFT JOIN PeerComp_tbl ON [2008Net_qry].GroupCode = PeerComp_tbl.GroupCode) LEFT JOIN 2008Gross_qry ON [2008Net_qry].FundID = [2008Gross_qry].FundID) LEFT JOIN 2006Gross_qry ON [2008Net_qry].FundID = [2006Gross_qry].FundID;

Appreciate the prompt feedback in advance.

[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

[popo725]

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]

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]

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.

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]

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]

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]

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