Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2007
    Posts
    1

    Unanswered: Divide by Zero error (Was "Ms Access")

    Not sure if this is the right place to put this..I'm trying to do a MS Access query. The basic formula is MONTHVAR%(MON0907-MON0906)/MON0906). This works fine is there are variables in both MON0907 and MON0906 whether positive or negative. However, MON0907 may be 0 (zero) or MON0906 may be 0 (zero). We may have no sales from September last year, MON0906, (0 value) and -8,266 MON0907, (the negative in this case means the goods were returned for September). Since 0 can't be divided an error keeps coming up. I've tried the IIF such as MONTHTODATE%: IIf([MON0907]=0,100,([mon0907]-[mon0906])/IIf([MON0906]=0,0,[mon0906])) but that's not working.
    IF MON0907 = 0 and MON0906 = 0 default = +100%.
    If MON0907 < 0 and MON0906 = 0 default = +100%.
    If MON0907 = 0 and MON0906 > 0 default = -100%.
    If MON0907 = 0 and MON0906 < 0 default = -100%.
    IF MON0907 > 0 and MON0906 = 0 default = +100%.

    I've tried coding a vba function but to no avail. Thank you.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Take a look at the Nz() function in the help files.
    Code:
    Nz(value, valueIfEmpty)
    Moved to the Microsoft Access topic.
    George
    Home | Blog

Posting Permissions

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