Results 1 to 10 of 10
  1. #1
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121

    Unhappy Divide by zero error

    ((PO.Quantity)/((EIN.TIER_QTY)*(EIN.TIE_QTY))) AS [PalletConfig]





    I am using in my select statement on SQL server but getting below errot. Can anybody fix it please.

    Msg 8134, Level 16, State 1, Line 1
    Divide by zero error encountered.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    How about:
    ((PO.Quantity)/(Nz((EIN.TIER_QTY),1)*Nz((EIN.TIE_QTY),1))) AS [PalletConfig]
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121
    Quote Originally Posted by Bob Fitz View Post
    How about:
    ((PO.Quantity)/(Nz((EIN.TIER_QTY),1)*Nz((EIN.TIE_QTY),1))) AS [PalletConfig]
    What is Nz is it funtion ?

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Adding on to Bob's answer,
    If [EIN.TIER_QTY] can actually be zero, I would try
    Code:
    ((PO.Quantity)/IIF(Nz((EIN.TIER_QTY),1)=0,1,(Nz((EIN.TIER_QTY),1)*Nz((EIN.TIE_QTY),1)))) AS [PalletConfig]

  5. #5
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121
    Quote Originally Posted by ssanfu View Post
    Adding on to Bob's answer,
    If [EIN.TIER_QTY] can actually be zero, I would try
    Code:
    ((PO.Quantity)/IIF(Nz((EIN.TIER_QTY),1)=0,1,(Nz((EIN.TIER_QTY),1)*Nz((EIN.TIE_QTY),1)))) AS [PalletConfig]
    what is NZ giving me error.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    removed post - needs more thought
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121
    Even simple iff statement is not working on my SQL management studio.

    DECLARE @Genderid INT
    SET @Genderid = 1
    SELECT IIF(@Genderid =1,'MALE', 'FEMALE') as Gender


    error:
    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near '='.

  8. #8
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm having some luck with
    POQuantity / IIf(Nz(EINTIER_QTY, 1) = 1, 1, EINTIER_QTY) * IIf(Nz(EINTIE_QTY, 1) = 1, 1, EINTIE_QTY)
    (I had to alter the table/field references to variable names I could assign values to since I don't have the data).

    If POQuantity = 100; EINTIER_QTY = 20; EINTIE_QTY = 5
    I get 25 as a result. If I make either or both EINTIER_QTY EINTIE_QTY Null I get varying answers. Not sure if that fits the bill.
    Not sure you should be arbitrarily computing values based on Nulls or zeros anyway. The best solution might be to ensure that table fields do not default to zero and cannot accept nulls, or code for the error and present a message that one of the values is missing or zero.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Micron,

    I just noticed that the OP is creating a query on SQL Server (Post #1)
    Quote Originally Posted by adnancanada View Post
    I am using in my select statement on SQL server but getting below errot. Can anybody fix it please.
    Will probably need to replace NZ (Access function) with NVL (SQL Function)

  10. #10
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I missed that too. Hope my structure provides some benefit anyway, as I came up with a different structure in the nested functions.
    Then again, there may be db design issues if values required for a calculation are allowed to have nulls or zeros. Maybe, maybe not.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. #num! when divide by 0 like to display 0
    By tareyj8569 in forum Access
    Replies: 5
    Last Post: 08-26-2015, 09:14 AM
  2. Divide report into four different areas
    By MyPaynes in forum Reports
    Replies: 1
    Last Post: 04-24-2014, 03:06 PM
  3. divide by zero
    By rbee in forum Access
    Replies: 11
    Last Post: 07-19-2012, 12:55 PM
  4. Divide two Fields, Total in another field
    By prawln in forum Access
    Replies: 1
    Last Post: 03-28-2011, 06:45 PM
  5. Replies: 0
    Last Post: 03-18-2010, 01:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums