Sum IIf Query ....

[Arvin]

Hello,

I have the following Sum IIf

test: Sum(IIf(Nz([StandardTime]=0),[SumOfTime]/60*100)+([StandardTime]*[SumOfOrders]/60*100))

the first part calculates .... and is fine

Sum(IIf(Nz([StandardTime]=0),[SumOfTime]/60*100)

the second part does nothing ..

)+([StandardTime]*[SumOfOrders]/60*100))

The Idea is that if there is no standard time specified then the formula should take [SumOfTime]/60*100 ..which it does .. then if there is a standard time specified then the formula should take [StandardTime]*[SumOfOrders]/60*100 ..but it doesn't ...

I have been trying to resolve this but cannot ...

Any help is appreciated ...thank you in advance

 

[plog]

I'm pretty sure it has to do with this: Nz([StandardTime]=0)

You are doing a comparison inside a function which makes little sense. What happens is [StandardTime]=0 will resolve to either True or False. Then what you are doing is taking that value (True or False) and running it through the Nz function. When that occurs it will always resolve to True because Nz(True) and Nz(False) both resolve to true and your IIf statement always tries to execute the code that follows.


I'm kind of confused as to what is to occur when StandardTime is null. What do you want your results to be then? Could you provide some sample data, your expected results and your actual results?

 

[Arvin]

thank you for responsding Plog, I appreciate it

I think got it to work .. I change the sum iif statement to

Hours Worked: Sum(IIf(Nz([StandardTime]=0)=0,0,[Sumoftime]/60*100)+([StandardTime]*[Sumoforders]/60*100))

this gives me the results I was looking for....