Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2003
    Posts
    126

    Unanswered: Calculation in report "Divide by 0" problem

    Hi all,

    I have been working with this particular report for a bit. I have a case where I need to divide two numbers... every now and again it's 0/something or 0/0, which gives me and error.

    How can I get around this???

    example:

    value1 = 2
    value2 = 4
    value3 = value2 / value1

    value3 would = 2

    another example

    value1 = 2
    value2 = 0
    value3 = value2 / value1

    value3 would = Divide by 0

    can I do some kind of case statement? I tried an iif:

    =IIf(Sum([BACKENDRETURNSTOTAL])>0,Sum([BACKENDRETURNSTOTAL]/[BACKENDRETURNS]),0)

    and I get an overflow...

    I want to just put a 0 in value3 if it can't be divided.

    Any help would be great!

  2. #2
    Join Date
    Jan 2003
    Posts
    15
    Value3=IIF(([Value2])=0,0,([Value1]/[Value2]))


    You might want to play with the Nz command as well. works really well with dealing with 0 errors.

  3. #3
    Join Date
    Jan 2003
    Posts
    126

    Talking IT WORKED!

    That took care of the problem, I sort of had that statment in there at one time!

    Thank you for the help!!!!

  4. #4
    Join Date
    Jan 2003
    Posts
    15
    Pleasure

  5. #5
    Join Date
    Jul 2003
    Posts
    42

    Unhappy Division By Zero in Query Expression

    I am trying to run an expression in a query to get the sum of certain values. When one of those is zero I get a divide by zero error. I tried doing the NZ function but cant figure it out. I am fairly new to working with numbers in databases, I am used to working with text type dbs and am struggling and would like some help if possible.

    Here are the items I am working with:

    TA/QY FIELD
    CR00 Current Std Cost
    CR00 12 Mo Usage (cost)
    CR00 Avg Mo Annual Usage (cost)
    TA01 Proposed Std Cost

    The sums are being developed from a two queries below QY01 ($Query_CROO-Sum Cost Data) and QY02 ($Query_CR00 - Saving Cost Data)

    QY01 No. Purchased per Year = Current Std Cost – 12 Mo Usage
    Query Expression: NoPurAnnually: Sum([TA_CR00 - Cost Reduction Main Table]![12 Mo Annual Usage]/[TA_CR00 - Cost Reduction Main Table]![Current STD Cost])

    QY01 No. Purchased per Month = Current Std Cost – Avg Mo Annual Usage
    Query Expression: NoPurMonthly: Sum([TA_CR00 - Cost Reduction Main Table]![Avg Mo Annual Usage]/[TA_CR00 - Cost Reduction Main Table]![Current STD Cost])

    QY01 Estimated New Year Cost = Proposed Std Cost – 12 Mo Usage
    Query Expression: EstNewYrCost: Sum([12 Mo Annual Usage]/[Proposed STD Cost])

    QY01 Estimated New Mo Cost = Avg Mo Annual Usage – Proposed Std Cost
    Query Expression: EstNewMoCost: Sum([Avg Mo Annual Usage]/[Proposed STD Cost])

    QY02 Estimated Annual Savings = pulls from the QY01 query above titled: $Query CROO-Sum Cost Data
    Query Expression: EstAnnualSvg: Sum([$Query_CR00 - Sum Cost Data]![12 Mo Annual Usage]-[$Query_CR00 - Sum Cost Data]![EstNewYrCost])

    QY02 Estimated Monthly Savings = pulls from the QY01 query above titled: $Query CROO-Sum Cost Data
    Query Expression: EstMonthlySvg: Sum([$Query_CR00 - Sum Cost Data]![Avg Mo Annual Usage]-[$Query_CR00 - Sum Cost Data]![EstNewMoCost])

    The problem is that Proposed Std Cost could be zero (default in the table sets it to zero in the field) while people are working on it OR it could be blank if it was deleted (which can be corrected with a filter) however I would rather it see the all the data. I cant get reports to work without it. The current above queries cant work unless data in that field is not zero.

    Questions:
    · Can someone please give me an example of a NZ query and tell me where it needs to go in the queries above in order for the reports to work.
    · Is there a way to combine these queries into one single query?
    · Is there a way to get the query data back into the main table once the query generates the numbers?

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    taking this one as an example
    Code:
    Query Expression: EstNewYrCost: Sum([12 Mo Annual Usage]/[Proposed STD Cost])
    change to:
    Code:
    Query Expression: EstNewYrCost: iif([Proposed STD Cost] = 0, "No Proposed STD Cost", Sum([12 Mo Annual Usage]/[Proposed STD Cost]))
    ....sorry to say that i'm not a fan of table/field/form/control/query names that contain spaces or hyphens.

    izy

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    "Is there a way to get the query data back into the main table once the query generates the numbers?"

    why would you want to do this when you can generate the calcs any time with the query?

    you would run the risk of someone changing (e.g.) a [Proposed Standard Cost], but your [EstNewYrCost] staying the same.

    yes of course you could re-run the query to update [EstNewYrCost], but to be CERTAIN you would have to do it every time you look at the table = even more of a hassle than generating the calcs with the query when you need the calcs.

    to answer your question strictly: yes. (almost?) anything you can do in a query can find it's way into a table (experiment with update queries after modifying the table structure to have fields for a calc). but do it for curiosity, not for real.

    izy
    Last edited by izyrider; 07-18-03 at 14:50.

  8. #8
    Join Date
    Jul 2003
    Posts
    42

    fixed - thank you

    Thanks to all for your previous help. I was able to get the divide by zero error corrected. I’m fairly new to this so the naming convention using spacing is wrong, but I will need to go with what I have for now and change naming convention as I get the opportunity and for the future stuff. Curious, why is name spacing not suggested?

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    myTextBox.value
    works fine but
    my Text Box.value
    doesn't

    either you dedicate your life to finding "[" and "]" on your keyboard, or you eliminate the spaces (and hyphens)

    [reading] AND [debugging a long SQL query littered] WITH [unnecessary square brackets] AND [spaces is more difficult]

    thanReadingTheSameStuffWithoutSpaces AND WithoutSquareBrackets

    izy

  10. #10
    Join Date
    Aug 2003
    Posts
    1

    Smile same problem, help!

    dear izyrider,
    hi, i got the same problem - Divide by zero error encountered.

    can you please help me.

    herewith i enclosed the store procedure as an attachment.

    thank you very much indeed.


    regards,
    Catcyc

    Originally posted by izyrider
    myTextBox.value
    works fine but
    my Text Box.value
    doesn't

    either you dedicate your life to finding "[" and "]" on your keyboard, or you eliminate the spaces (and hyphens)

    [reading] AND [debugging a long SQL query littered] WITH [unnecessary square brackets] AND [spaces is more difficult]

    thanReadingTheSameStuffWithoutSpaces AND WithoutSquareBrackets

    izy
    Attached Files Attached Files

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    your only division is in the --cast part of the code:
    Code:
    	--cast(
     	  Sum(Case
    		LOGICALTEST1  then 1
    		Else 0
    	      End) * 100 / 
    	   Sum(Case
    		LOGICALTEST2 then 1
    		else 0
    	    end)
            --as numeric(3,2))
    you are clearly setting yourself up for a fall with the second else 0



    could you consider something like (pseudo code)
    Code:
    if (LOGICALTEST1 AND LOGICALTEST2) then
    	--cast(
    		...etc etc as before
    	--as numeric(3,2))
    else
    	0
    endif
    izy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •