Wrox Programmer Forums IIf Statement "division by zero" error
 | Search | Today's Posts | Mark Forums Read
BOOK: Beginning Access 2003 VBA
 This is the forum to discuss the Wrox book Beginning Access 2003 VBA by Denise M. Gosnell; ISBN: 9780764556593 Read more about Beginning Access 2003 VBA or buy the book from your favorite retailer Download the code for Beginning Access 2003 VBA
 Welcome to the p2p.wrox.com Forums. You are currently viewing the BOOK: Beginning Access 2003 VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
September 27th, 2005, 10:54 AM
 Friend of Wrox Join Date: Sep 2005 Location: , , USA. Posts: 106 Thanks: 0 Thanked 0 Times in 0 Posts
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

September 28th, 2005, 06:03 AM
 Registered User Join Date: Sep 2005 Location: , , . Posts: 3 Thanks: 0 Thanked 0 Times in 0 Posts

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.
September 28th, 2005, 11:11 AM
 Friend of Wrox Join Date: Sep 2005 Location: , , USA. Posts: 106 Thanks: 0 Thanked 0 Times in 0 Posts

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

September 29th, 2005, 06:07 AM
 Registered User Join Date: Sep 2005 Location: , , . Posts: 3 Thanks: 0 Thanked 0 Times in 0 Posts

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

September 29th, 2005, 11:14 AM
 Friend of Wrox Join Date: Sep 2005 Location: , , USA. Posts: 106 Thanks: 0 Thanked 0 Times in 0 Posts

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;

September 30th, 2005, 05:48 AM
 Registered User Join Date: Sep 2005 Location: , , . Posts: 3 Thanks: 0 Thanked 0 Times in 0 Posts

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.

 Similar Threads Thread Thread Starter Forum Replies Last Post Can I make this a IIF Statement Corey Access VBA 1 November 3rd, 2006 08:40 AM Iif statement in Access rangeview Access 7 March 28th, 2006 01:14 PM IIF Statement golden Access 2 August 3rd, 2004 01:32 AM IIF Statement golden Access 3 July 12th, 2004 07:49 PM IIF Statement Question fastcorvette Access 8 October 31st, 2003 11:01 PM