Report Overflow Error

B

Brandon M

I have encountered an error and I am hoping that it is because I have done
something silly. The report is pulling information from a query which when
pulled gives no errors. The report pulls totals from the query based on a
name field. When I open the report in layout preview mode it shows
perfectly. When I open the report in print preview mode it crashes access
completely. When it is executed from vb code it gives an overflow error. Any
ideas?

I am using Access 2002. Here is the query the report is pulling from then
the query the report is using. I don't think it has anything to do with the
queries I just added them in case... Thank-you!

SELECT tblEmployeeStats.OpName, tblEmployeeStats.ScheduledTime,
[OnTime]/60000 AS OnTimeMin, [OnTime]/60000 AS TalkTimeMin,
[OnTime]/([ScheduledTime]*3600000) AS OnvsSched, [TalkTime]/[OnTime] AS
OnvsTalk, [HiLighted]/[OnTime] AS OnvsActive, ([OnvsSched]+[OnvsActive])/2
AS TimeEff, tblEmployeeStats.CallsAssigned, tblEmployeeStats.CallsAnswered,
([OnTime]/60000)/[CallsAnswered] AS CallFreq,
[CallsAnswered]/([OnTime]/60000) AS CallPerMin, tblEmployeeStats.Reassigns,
[Reassigns]/[CallsAssigned] AS ReassignPcnt, [CallsAssigned]-[CallsAnswered]
AS Abandon, ([CallsAssigned]-[CallsAnswered])/[CallsAssigned] AS
AbandonPcnt, tblEmployeeStats.Reverts, tblEmployeeStats.AutoAns,
tblEmployeeStats.CallsHold, tblEmployeeStats.CallsNoMsg,
tblEmployeeStats.AverageTTA, tblEmployeeStats.Disconnects,
tblEmployeeStats.Monitoring,
([CallsAnswered]/([CallsAssigned]+[Reassigns]))-[AutoAns] AS CallEff,
(([CallsAnswered]/([CallsAssigned]+[Reassigns]))-[AutoAns])*(1+((0.5-[Discon
nects])/10)) AS CallEffDisc,
((([OnvsSched]+[OnvsActive])/2)+(([CallsAnswered]/([CallsAssigned]+[Reassign
s]))-[AutoAns])*(1+((0.5-[Disconnects])/10)))/2 AS Overall
FROM tblEmployeeStats
WHERE
(((tblEmployeeStats.StatDate)>=CDate([forms]![frmOperatorStats]![txtStartDat
e]) And
(tblEmployeeStats.StatDate)<=CDate([forms]![frmOperatorStats]![txtEndDate]))
);

And the report query:
SELECT qryEmployeeStatAll.OpName, Sum(qryEmployeeStatAll.ScheduledTime) AS
SumOfScheduledTime, Sum(qryEmployeeStatAll.OnTimeMin) AS SumOfOnTimeMin,
Sum(qryEmployeeStatAll.TalkTimeMin) AS SumOfTalkTimeMin,
Avg(qryEmployeeStatAll.OnvsSched) AS AvgOfOnvsSched,
Avg(qryEmployeeStatAll.OnvsTalk) AS AvgOfOnvsTalk,
Avg(qryEmployeeStatAll.OnvsActive) AS AvgOfOnvsActive,
Avg(qryEmployeeStatAll.TimeEff) AS AvgOfTimeEff,
Sum(qryEmployeeStatAll.CallsAssigned) AS SumOfCallsAssigned,
Sum(qryEmployeeStatAll.CallsAnswered) AS SumOfCallsAnswered,
Avg(qryEmployeeStatAll.CallFreq) AS AvgOfCallFreq,
Avg(qryEmployeeStatAll.CallPerMin) AS AvgOfCallPerMin,
Sum(qryEmployeeStatAll.Reassigns) AS SumOfReassigns,
Avg(qryEmployeeStatAll.ReassignPcnt) AS AvgOfReassignPcnt,
Sum(qryEmployeeStatAll.Abandon) AS SumOfAbandon,
Avg(qryEmployeeStatAll.AbandonPcnt) AS AvgOfAbandonPcnt,
Sum(qryEmployeeStatAll.Reverts) AS SumOfReverts,
Avg(qryEmployeeStatAll.AutoAns) AS AvgOfAutoAns,
Avg(qryEmployeeStatAll.CallsHold) AS AvgOfCallsHold,
Avg(qryEmployeeStatAll.CallsNoMsg) AS AvgOfCallsNoMsg,
Avg(qryEmployeeStatAll.AverageTTA) AS AvgOfAverageTTA,
Avg(qryEmployeeStatAll.Disconnects) AS AvgOfDisconnects,
Avg(qryEmployeeStatAll.Monitoring) AS AvgOfMonitoring,
Avg(qryEmployeeStatAll.CallEff) AS AvgOfCallEff,
Avg(qryEmployeeStatAll.CallEffDisc) AS AvgOfCallEffDisc,
Avg(qryEmployeeStatAll.Overall) AS AvgOfOverall FROM qryEmployeeStatAll
GROUP BY qryEmployeeStatAll.OpName;
 
D

Duane Hookom

I try to avoid using derived columns as parts of other expressions.
For instance, I would change this snippet
[HiLighted]/[OnTime] AS OnvsActive, ([OnvsSched]+[OnvsActive])/2
to
[HiLighted]/[OnTime] AS OnvsActive, ([OnvsSched]+([HiLighted]/[OnTime]))/2
There are possibly other examples but I didn't search your entire sql for
fields vs calculated expressions.

--
Duane Hookom
MS Access MVP


Brandon M said:
I have encountered an error and I am hoping that it is because I have done
something silly. The report is pulling information from a query which when
pulled gives no errors. The report pulls totals from the query based on a
name field. When I open the report in layout preview mode it shows
perfectly. When I open the report in print preview mode it crashes access
completely. When it is executed from vb code it gives an overflow error. Any
ideas?

I am using Access 2002. Here is the query the report is pulling from then
the query the report is using. I don't think it has anything to do with the
queries I just added them in case... Thank-you!

SELECT tblEmployeeStats.OpName, tblEmployeeStats.ScheduledTime,
[OnTime]/60000 AS OnTimeMin, [OnTime]/60000 AS TalkTimeMin,
[OnTime]/([ScheduledTime]*3600000) AS OnvsSched, [TalkTime]/[OnTime] AS
OnvsTalk, [HiLighted]/[OnTime] AS OnvsActive, ([OnvsSched]+[OnvsActive])/2
AS TimeEff, tblEmployeeStats.CallsAssigned, tblEmployeeStats.CallsAnswered,
([OnTime]/60000)/[CallsAnswered] AS CallFreq,
[CallsAnswered]/([OnTime]/60000) AS CallPerMin, tblEmployeeStats.Reassigns,
[Reassigns]/[CallsAssigned] AS ReassignPcnt, [CallsAssigned]-[CallsAnswered]
AS Abandon, ([CallsAssigned]-[CallsAnswered])/[CallsAssigned] AS
AbandonPcnt, tblEmployeeStats.Reverts, tblEmployeeStats.AutoAns,
tblEmployeeStats.CallsHold, tblEmployeeStats.CallsNoMsg,
tblEmployeeStats.AverageTTA, tblEmployeeStats.Disconnects,
tblEmployeeStats.Monitoring,
([CallsAnswered]/([CallsAssigned]+[Reassigns]))-[AutoAns] AS CallEff,
(([CallsAnswered]/([CallsAssigned]+[Reassigns]))-[AutoAns])*(1+((0.5-[Discon
nects])/10)) AS CallEffDisc,
((([OnvsSched]+[OnvsActive])/2)+(([CallsAnswered]/([CallsAssigned]+[Reassign
s]))-[AutoAns])*(1+((0.5-[Disconnects])/10)))/2 AS Overall
FROM tblEmployeeStats
WHERE
(((tblEmployeeStats.StatDate)>=CDate([forms]![frmOperatorStats]![txtStartDat
e]) And
(tblEmployeeStats.StatDate)<=CDate([forms]![frmOperatorStats]![txtEndDate]))
);

And the report query:
SELECT qryEmployeeStatAll.OpName, Sum(qryEmployeeStatAll.ScheduledTime) AS
SumOfScheduledTime, Sum(qryEmployeeStatAll.OnTimeMin) AS SumOfOnTimeMin,
Sum(qryEmployeeStatAll.TalkTimeMin) AS SumOfTalkTimeMin,
Avg(qryEmployeeStatAll.OnvsSched) AS AvgOfOnvsSched,
Avg(qryEmployeeStatAll.OnvsTalk) AS AvgOfOnvsTalk,
Avg(qryEmployeeStatAll.OnvsActive) AS AvgOfOnvsActive,
Avg(qryEmployeeStatAll.TimeEff) AS AvgOfTimeEff,
Sum(qryEmployeeStatAll.CallsAssigned) AS SumOfCallsAssigned,
Sum(qryEmployeeStatAll.CallsAnswered) AS SumOfCallsAnswered,
Avg(qryEmployeeStatAll.CallFreq) AS AvgOfCallFreq,
Avg(qryEmployeeStatAll.CallPerMin) AS AvgOfCallPerMin,
Sum(qryEmployeeStatAll.Reassigns) AS SumOfReassigns,
Avg(qryEmployeeStatAll.ReassignPcnt) AS AvgOfReassignPcnt,
Sum(qryEmployeeStatAll.Abandon) AS SumOfAbandon,
Avg(qryEmployeeStatAll.AbandonPcnt) AS AvgOfAbandonPcnt,
Sum(qryEmployeeStatAll.Reverts) AS SumOfReverts,
Avg(qryEmployeeStatAll.AutoAns) AS AvgOfAutoAns,
Avg(qryEmployeeStatAll.CallsHold) AS AvgOfCallsHold,
Avg(qryEmployeeStatAll.CallsNoMsg) AS AvgOfCallsNoMsg,
Avg(qryEmployeeStatAll.AverageTTA) AS AvgOfAverageTTA,
Avg(qryEmployeeStatAll.Disconnects) AS AvgOfDisconnects,
Avg(qryEmployeeStatAll.Monitoring) AS AvgOfMonitoring,
Avg(qryEmployeeStatAll.CallEff) AS AvgOfCallEff,
Avg(qryEmployeeStatAll.CallEffDisc) AS AvgOfCallEffDisc,
Avg(qryEmployeeStatAll.Overall) AS AvgOfOverall FROM qryEmployeeStatAll
GROUP BY qryEmployeeStatAll.OpName;
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

OCI-22053 Overflow error 10
Report Error: Overflow 1

Top