Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > BOOK: Beginning Access 2003 VBA
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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

Reply With Quote
  #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.
Reply With Quote
  #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

Reply With Quote
  #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?



Reply With Quote
  #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;


Reply With Quote
  #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.




Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 06:51 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.