p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   BOOK: Beginning Access 2003 VBA (http://p2p.wrox.com/forumdisplay.php?f=150)
-   -   IIf Statement "division by zero" error (http://p2p.wrox.com/showthread.php?t=33759)

Corey September 27th, 2005 10:54 AM

IIf Statement "division by zero" error
 
I am getting a division by zero error when trying to run a query in Microsoft access 2003. Is there an easy work around this error? Maybe an iif statement?

Thanks

Corey


SteveH September 28th, 2005 06:03 AM

Corey,

The IIF (Built-In) function in Access won't give you the results you are looking for. If you try to use the function, it will generate an #Error as well. To resolve this issue I created a function. Here's the code:

Function DivideByZero(Numerator As Double, Denominator As Double) As Double

    Dim dblResults As Double

    If Numerator = 0 Or Denominator = 0 Then
        dblResults = 0
    Else
        dblResults = Numerator / Denominator
    End If

    DivideByZero = dblResults

End Function

The statement will return the value of zero if either input is zero.

Corey September 28th, 2005 11:11 AM

SteveH,

Thank you for taking the time to help me resolve this error.
After adding the code you supplied.

I’m now receiving a error: “Compile error. In query expression’ (((SM_PO.PO_EXPTD_DT) Between Date()-30 And Date()+30)AND((SM_PRODUCT.PCTTL_COST).0))’.

Any idea how I can work this one out also?

Thank's

Corey


doggiebore September 29th, 2005 06:07 AM

Can you post what the code is thats geberating the error (with field types)? Are you doing something with dates and money?




Corey September 29th, 2005 11:14 AM

Yes most of the fields that use any formulas are dates and currency

Below is the SQL code:

SELECT DISTINCT Buyer.[Buyer Name], SM_PO.PO_EXPTD_DT AS [Expected Date], SM_PO.PO_PO_NUM AS [PO#], SM_SUPPLIER.S_NAME AS Vendor, SM_PRODUCT.P_STYL AS [Item Number], SM_PRODUCT.P_DESC AS Description, SM_PRODUCT.P_CTRL_COST AS [Unit Cost], SM_PRODUCT.P_CTRL_RETAIL AS [Retail Rrice], SM_PO_LN.POL_QTY_ORD AS Qty, ([P_CTRL_RETAIL]-[P_CTRL_COST]) AS [Mark Up], ([p_ctrl_retail]-[P_Ctrl_cost])/[p_ctrl_retail] AS GM, ([P_CTRL_COST]*[POL_QTY_ORD]) AS [Extended Cost], ([P_CTRL_RETAIL]*[POL_QTY_ORD]) AS [Retail Potential], SM_PRODUCT.P_STAT_I AS Status, Last(Tracking_Table.Date) AS [Date], Last(Tracking_Table.Tracking) AS Tracking, Last(Tracking_Table.Status) AS [Note] INTO Landing_A1_tbl
FROM Tracking_Table RIGHT JOIN (((SM_PRODUCT RIGHT JOIN SM_PO_LN ON SM_PRODUCT.P_PROD_INT_NUM = SM_PO_LN.POL_PROD_INT_NUM) LEFT JOIN (SM_PO LEFT JOIN SM_SUPPLIER ON SM_PO.PO_SUP_CD = SM_SUPPLIER.S_SUP_CD) ON SM_PO_LN.POL_PO_NUM = SM_PO.PO_PO_NUM) LEFT JOIN Buyer ON SM_PRODUCT.P_CLASS_CD_2 = Buyer.[Buyer #]) ON Tracking_Table.PO = SM_PO_LN.POL_PO_NUM
GROUP BY Buyer.[Buyer Name], SM_PO.PO_EXPTD_DT, SM_PO.PO_PO_NUM, SM_SUPPLIER.S_NAME, SM_PRODUCT.P_STYL, SM_PRODUCT.P_DESC, SM_PRODUCT.P_CTRL_COST, SM_PRODUCT.P_CTRL_RETAIL, SM_PO_LN.POL_QTY_ORD, ([P_CTRL_RETAIL]-[P_CTRL_COST]), ([p_ctrl_retail]-[P_Ctrl_cost])/[p_ctrl_retail], ([P_CTRL_COST]*[POL_QTY_ORD]), ([P_CTRL_RETAIL]*[POL_QTY_ORD]), SM_PRODUCT.P_STAT_I, SM_PRODUCT.P_CLASS_CD_2
HAVING (((SM_PO.PO_EXPTD_DT) Between Date()-30 And Date()+30) AND ((SM_PRODUCT.P_CTRL_COST)>0))
ORDER BY SM_PO.PO_EXPTD_DT, SM_PRODUCT.P_STYL;



doggiebore September 30th, 2005 05:48 AM

Can't see anything glaringly obvious. The actual condition seems to work ok when I tried it (ok, on a small table).

I'd try a simple query first just using the basic query tool add the two tables SM_PRODUCT & SM_PO and join them (does that need SM_PO_LN? then three tables)
Select a field to display from each table and then add the conditions. You should be able to see the SQL built to confirm the condition looks the same. Try running it, see if it works. Sometimes I've found the errors reported don't actually relate to the true cause of the problem.






All times are GMT -4. The time now is 02:16 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.