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.