Report Overflow Error

  • Thread starter Thread starter Brandon M
  • Start date Start date
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;
 
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...
 
Back
Top