Expression builder

  • Thread starter Thread starter Billy
  • Start date Start date
B

Billy

First, Thank you for taking our time to read and answer my
questions, I appreciate your assistance.

I created an expression in a querie that looks like this:

Hours-of-Effort/Finding: ([Total Hours]/(([SumOfPHS
Initiated By]/6)+([SumOfPIRS Initiated By]/4)+[SumOfCAR
Level]+1.5))

What this expression does is basically takes the "total
hours" and divides it by some arbitrary mathematical
expression:

([SumOfPHS Initiated By]/6)+([SumOfPIRS Initiated By]/4)+
[SumOfCAR Level]+1.5)

I need to prevent the denominator (the above expression)
from ever being 0. I would like to:

1)verify that the denominator is not zero, if its not zero
then I would like to add .5 to the denominator before the
expression is divided.

2)if the denominator is zero, then I would like to set the
denominator to 1.5 and then divide.

IN C-LANGUAGE the logic statement would read:

IF the denominator is 0 then divide by 1.5
ELSE add .5 to the denominator before dividing

THANKS a bunch,

Billy
 
Billy,

I think this will give what you want...
Hours-of-Effort/Finding: ([Total Hours]/((([SumOfPHS Initiated
By]/6)+([SumOfPIRS Initiated By]/4)+[SumOfCAR
Level]+1.5))+IIf((([SumOfPHS Initiated By]/6)+([SumOfPIRS Initiated
By]/4)+[SumOfCAR Level]+1.5))=0,1.5,0.5))

By the way, it is not a good idea to put a - or a / as part of a field
or field alias name.

- Steve Schapel, Microsoft Access MVP
 
Back
Top