Results 1 to 5 of 5

Thread: Divide by zero

  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Unanswered: Divide by zero

    I am trying to compute a percentage change in growth as an alias in a query

    Growth: Format([the_change]/[totalamount],"Percent")

    works great unless my totalamount = 0

    I tried an IIF but did not seem to work - any Ideas - I know Geo probably has a function for this
    Dale Houston, TX

  2. #2
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    got it sorry

    Growth: IIf([totalamount]>0,Format([the_change]/[totalamount],"Percent"),"Cannot divide by zero")
    Dale Houston, TX

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by axsprog
    I know Geo probably has a function for this
    Personally I would go with a CASE statement in the SQL window; not an Iif() (because IIf's are dbms specific (perhaps even access only!)).

    Anyhow, I would have asked you "What do you want to display when there is a 0?"
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    unless you need the value in the query (say as part of a sort sequence or other calculation) Id be tempted to leave the calculation to the presentation layer.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    That layer returns #Error
    My clients are so dense they would not see that there is a zero in the report , so I actuall place a "No Data for Time Period" where there were divide by zeros - works fine - no clients have called as of yet asking me what no data means - so I guess I am lucky.
    Dale Houston, TX

Posting Permissions

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