Year Over Year Variance #Error (1 Viewer)

A

ALewis06

Registered User.

Local time

Today, 01:45

Joined

Jun 21, 2012

Messages

124

• Apr 28, 2016

• #1

I am trying to do a very simple calculation in a query to determine year over year variance displayed as a percentage:

TotYoYVar%: ([2016FcstTotal]-[2015Total])/[2015Total]

How can I prevent Access from returning a result of #Error when [2015Total] is 0?

 

C

CJ_London

Super Moderator

Staff member

Local time

Today, 06:45

Joined

Feb 19, 2013

Messages

16,607

• Apr 28, 2016

• #2

use an iif statement

TotYoYVar%: iif([2015Total]=0,0,([2016FcstTotal]-[2015Total])/[2015Total])

note, generally not a good idea to use non alphanumeric characters in field names

 

A

ALewis06

Registered User.

Local time

Today, 01:45

Joined

Jun 21, 2012

Messages

124

• Apr 28, 2016

• #3

Worked perfectly! Thanks

 

jdraw

Super Moderator

Staff member

Local time

Today, 01:45

Joined

Jan 23, 2006

Messages

15,379

• Apr 28, 2016

• #4

ALewis06,

Just a little info on the why of the issue and CJ's solution.
You really can not divide by 0 (get error attempt to divide by 0).

So as CJ said, check the divisor (/[2015Total]),
if it is 0, set the expression to 0, otherwise do the calculation.

Good luck.