CODEWITHSUNDEEP
sqlms-access
Fordy
Fordy

Reputation: 97

MS Access 'Overflow' On Query

I have been using a query to gather my monthly stats for around 6months - this has worked fine until today where I am getting an Overflow error.

See below the SQL view if anyone can help?

SELECT 
    [_SubEmailData_Month].[Processor Location], 
    [_SubEmailData_Month].SumOfDue_In_Month_Cases, 
    [_SubEmailData_Month].SumOfCompleted_Cases, 
    [_SubEmailData_Month].SumOfSLA_Cases, 
    IIf([SumOfDue_In_Month_Cases]=0,"",[SumOfSLA_Cases]/[SumOfCompleted_Cases]) AS In_SLA, 
    IIf([SumOfDue_In_Month_Cases]=0,"",1-[SumOfSLA_Cases]/[SumOfCompleted_Cases]) AS Out_SLA, 
    [_SubEmailData_Month].SumOfWIP_Cases, 
    [_SubEmailData_Month].SumOfOverdue_Cases
FROM 
    _SubEmailData_Month
GROUP BY 
    [_SubEmailData_Month].[Processor Location], 
    [_SubEmailData_Month].SumOfDue_In_Month_Cases, 
    [_SubEmailData_Month].SumOfCompleted_Cases, 
    [_SubEmailData_Month].SumOfSLA_Cases, 
    IIf([SumOfDue_In_Month_Cases]=0,"",[SumOfSLA_Cases]/[SumOfCompleted_Cases]), 
    IIf([SumOfDue_In_Month_Cases]=0,"",1-SumOfSLA_Cases]/[SumOfCompleted_Cases]), 
    [_SubEmailData_Month].SumOfWIP_Cases, 
    [_SubEmailData_Month].SumOfOverdue_Cases;

Upvotes: 1

Views: 366

Answers (1)

Gustav
Gustav

Reputation: 55831

Maybe you divide by zero. You could check for that:

IIf([SumOfDue_In_Month_Cases]=0 Or [SumOfCompleted_Cases]=0,Null,[SumOfSLA_Cases]/[SumOfCompleted_Cases]) AS In_SLA,

Upvotes: 1

Related Questions