Wrox Programmer Forums
| 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
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
  #1 (permalink)  
Old 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
Send a message via Yahoo to Corey
Default 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

  #2 (permalink)  
Old September 28th, 2005, 06:03 AM
Registered User
 
Join Date: Sep 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
  #3 (permalink)  
Old 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
Send a message via Yahoo to Corey
Default

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

  #4 (permalink)  
Old September 29th, 2005, 06:07 AM
Registered User
 
Join Date: Sep 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



  #5 (permalink)  
Old 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
Send a message via Yahoo to Corey
Default

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;


  #6 (permalink)  
Old September 30th, 2005, 05:48 AM
Registered User
 
Join Date: Sep 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





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