What does "overflow" mean? 2

[kentwoodjean]

I have a couple of reports that worked great during the development. Now, they give me an error that says "Overflow". The queries continue to work just fine. Can someone tell me what the error means so I can maybe correct it? The help file says nothing on this.

 

[dhookom]

You possibly are dividing by zero or performing some other similar calculation either in a control source or the query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[KenReay]

Hi

Or exceeding the possible value to be held in a given variable type. Most likely is arithmetic in an integer variable and exceeding the 32767 maximum value

If the queries work, check the report for calculated fields which may be the cause

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[kentwoodjean]

Thanks Ken and Ray. You were correct in that the problem was with a calculation in my report. In checking my totals and average lines that occur monthly on this daily report, I found the problem. One by one I eliminated each calculation and kept trying the report until I found the bad one.

All is fine now. Thanks to both of you.

 

[kentwoodjean]

I guess I spoke too soon, all is not completely well but maybe Ken, Duane or someone else can advise. What was wrong was that I was trying to average a column of averages, causing the overflow problem. What I still need to figure out is how to get some type of average which can be used as a general guide of performance. Here is how I arrived at my average in the first place:

Incoming telephone lines each w/own measurements (example)
Line 1 = 89.2 speed of answer (ASA)and 59 calls handled
Line 2 - 41.7 ASA and 76 calls handled
Line 3 - 14.2 ASA and 992 calls handled
In order to get a weighted average, created an expression by multiplying the ASA x # calls handled for each line. In another query, took the sum from the expression above for the 3 lines and divided it by the sum of the total calls for a new ASA for all 3 lines combined, or weighted average.That all works fine.
This is a daily report, and the users would like to see an "Average" line at the end of each month to get a general idea of what is the norm. Since I can't average an average as in the example above, is there another way I can do this?

I can show you my SQl statements for both queries, although they are rather long.

Query 1

SELECT DailyAppl.FromDate, DailyAppl.ToDate, DailyAppl.TSF, DailyAppl.TotOffer, DailyAppl.TotHandled, DailyAppl.TotAband, DailyAppl.ASA, IIf([TotOffer]=0,0,[TotAband]/[TotOffer]) AS canc, [TSF]*[TotHandled]/100 AS Ans, [ASA]*[TotHandled] AS [As], DailyAppl.ATT, [TotOffer]*[WRAP] AS Aw, [ATT]*[TotHandled] AS At
FROM APPL_tblXref INNER JOIN DailyAppl ON APPL_tblXref.App = DailyAppl.App
GROUP BY DailyAppl.FromDate, DailyAppl.ToDate, DailyAppl.TSF, DailyAppl.TotOffer, DailyAppl.TotHandled, DailyAppl.TotAband, DailyAppl.ASA, DailyAppl.ATT, DailyAppl.WRAP, APPL_tblXref.App
HAVING (((APPL_tblXref.App) In (9,20,23,26,28,30,32,48,51,53,105,126,129,130,133,134,136,138,139,140,141,148,149,156,247,248,300)));


Query 2 (Report Query)

SELECT [CS DailyTot].ToDate, [SumOfAns]/[SumOfTotHandled] AS AvTSF, Sum([CS DailyTot].TotOffer) AS SumOfTotOffer, Sum([CS DailyTot].TotHandled) AS SumOfTotHandled, Sum([CS DailyTot].TotAband) AS SumOfTotAband, [SumofTotAband]/[SumOfTotOffer] AS PercentOfCanc, Sum([CS DailyTot].As) AS SumOfAs, [SumOfAs]/[SumOfTotHandled] AS ASA, Sum([CS DailyTot].Ans) AS SumOfAns, Sum([CS DailyTot].ATT) AS SumOfATT, [SumOfAw]/[SumOfTotOffer] AS Wrap, Sum([CS DailyTot].Aw) AS SumOfAw, [SumOfAt]/[SumOfTotHandled] AS ATT, Sum([CS DailyTot].At) AS SumOfAt
FROM [CS DailyTot]
GROUP BY [CS DailyTot].ToDate
HAVING ((([CS DailyTot].ToDate) Between #1/1/2004# And #12/31/2004#));

 

[dhookom]

It looks like you do a fair amount of divisions and calculations. Are you sure that you won't ever divide by zero? Also, do you have a table with unique values of App so you can add a field that determines which records to include in Query1 without the huge "In (...)" clause.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kentwoodjean]

Each App always has numbers associated with them so I have never had a situation where I would divide by 0.

And yes, I do have a table where I recently added a field that would eliminate the need to include all the #'s you refer to. Just havn't had time to change all my queries.

 

[dhookom]

In query2, you calculate a sum and use its alias in another calculation. I always avoid doing this. I don't know if it is going to make any difference for you but it is always something I avoid.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.