Overflow Error

[You Da Man]

I keep getting an overflow error in some queries that I am running. It is
probably from a division by zero problem. I get a #error in the formula.
Any suggestions on how to fix this?

 

[Ken Snell [MVP]]

You mean, other than to "not" do the divide by zero at all? < g >

Post the SQL statement of the query and identify where you think the error
is occurring.

 

[You Da Man]

I have to do the divide by 0 because I have other data that works and does
not divide by 0. I am just wondering if there is a way to make the #error
that comes up with the division by 0 error go away like you can in Excel with
the iserror() statement or other alternatives. I only have an overflow error
when I try to run a query that uses a greater than a certain percentage or if
I try to filter the data somehow -- which is key to analyzing this data. I
am trying to break the data down so I can see what is happening with about
35,000 products that were sold.

 

[You Da Man]

Here is the SQL statement.
SELECT [Fiscal 2005 Price File OFFICIAL V1].NIIN, [Fiscal 2005 Price File
OFFICIAL V1].[ACT-CD], [Fiscal 2005 Price File OFFICIAL V1].[2005 LAC], [FY04
Std Exc LAC LRC].[2004 LAC Amt], [Fiscal 2005 Price File OFFICIAL V1]![2005
LAC]-[FY04 Std Exc LAC LRC]![2004 LAC Amt] AS [LAC Difference], ([Fiscal 2005
Price File OFFICIAL V1]![2005 LAC]-[FY04 Std Exc LAC LRC]![2004 LAC
Amt])/[FY04 Std Exc LAC LRC]![2004 LAC Amt] AS [LAC % Change], [Fiscal 2005
Price File OFFICIAL V1].[2005 LRC], [FY04 Std Exc LAC LRC].[2004 LRC Amt],
[Fiscal 2005 Price File OFFICIAL V1]![2005 LRC]-[FY04 Std Exc LAC LRC]![2004
LRC Amt] AS [LRC Difference], ([Fiscal 2005 Price File OFFICIAL V1]![2005
LRC]-[FY04 Std Exc LAC LRC]![2004 LRC Amt])/[FY04 Std Exc LAC LRC]![2004 LRC
Amt] AS [LRC % Change]
FROM [Fiscal 2005 Price File OFFICIAL V1] INNER JOIN [FY04 Std Exc LAC LRC]

 

[Douglas J. Steele]

Try:

IIf([FY04 Std Exc LAC LRC]![2004 LAC Amt] = 0, 0, ([Fiscal 2005 Price File
OFFICIAL V1]![2005 LAC]-[FY04 Std Exc LAC LRC]![2004 LAC Amt])/[FY04 Std Exc
LAC LRC]![2004 LAC Amt]) AS [LAC % Change]

and

IIf([FY04 Std Exc LAC LRC]![2004 LRC Amt] = 0, 0, ([Fiscal 2005 Price File
OFFICIAL V1]![2005 LRC]-[FY04 Std Exc LAC LRC]![2004 LRC Amt])/[FY04 Std Exc
LAC LRC]![2004 LRC Amt]) AS [LRC % Change]

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Here is the SQL statement.
SELECT [Fiscal 2005 Price File OFFICIAL V1].NIIN, [Fiscal 2005 Price File
OFFICIAL V1].[ACT-CD], [Fiscal 2005 Price File OFFICIAL V1].[2005 LAC], [FY04
Std Exc LAC LRC].[2004 LAC Amt], [Fiscal 2005 Price File OFFICIAL V1]![2005
LAC]-[FY04 Std Exc LAC LRC]![2004 LAC Amt] AS [LAC Difference], ([Fiscal 2005
Price File OFFICIAL V1]![2005 LAC]-[FY04 Std Exc LAC LRC]![2004 LAC
Amt])/[FY04 Std Exc LAC LRC]![2004 LAC Amt] AS [LAC % Change], [Fiscal 2005
Price File OFFICIAL V1].[2005 LRC], [FY04 Std Exc LAC LRC].[2004 LRC Amt],
[Fiscal 2005 Price File OFFICIAL V1]![2005 LRC]-[FY04 Std Exc LAC LRC]![2004
LRC Amt] AS [LRC Difference], ([Fiscal 2005 Price File OFFICIAL V1]![2005
LRC]-[FY04 Std Exc LAC LRC]![2004 LRC Amt])/[FY04 Std Exc LAC LRC]![2004 LRC
Amt] AS [LRC % Change]
FROM [Fiscal 2005 Price File OFFICIAL V1] INNER JOIN [FY04 Std Exc LAC LRC]

You mean, other than to "not" do the divide by zero at all? < g >

Post the SQL statement of the query and identify where you think the error
is occurring.

 

[Mike Schlosser]

Instead of placing the calculation into your query, write a function, and
call that function from the query.

= AvoidOverFlowError([DivideThisField_Name] , [DivideByField_Name])

IN a module past this code, use Double type if you want.

Public Function AvoidOverFlowError(DivideThis As Double, DivideBy As Double)
' You cannot use an immediate IIF.
' DivideByZeroPlease = iif(DivideBy = 0 , 0, DivideThis / DivideBy)
' IIF() would still return an error, since the entire phrase is parsed
out.
' If it did work you could use it in the query and not need code.

If DivideBy = 0 Then
AvoidOverFlowError = 0
Exit Function
Else
AvoidOverFlowError = DivideThis / DivideBy
End If

End Function


Mike Schlosser