IIf Nz

[jlute]

Hello all you MVP's and Access enthusiasts!

I have a tricky one that I can't resolve and thought maybe someone
could help!

If [Ti] is "0" then [Hi] + [AdditionalUnits]
If [Hi] is "0" then [Ti] + [AdditionalUnits]
If [Ti] is "1" or more and [Hi] is "1" or more then [Ti]*[Hi]+
[AdditionalUnits]

I came up with this but it's not returning as desired:
UnitLoadTotal: IIf(Nz([Ti],0)<>0,IIf(Nz([Hi],1)<>1,[Hi]+
[AdditionalUnits],IIf(Nz([Ti],1)<>1,IIf(Nz([Hi],0)<>0,[Ti]+
[AdditionalUnits],IIf(Nz([Ti],1)<>1,IIf(Nz([Hi],1)<>1,[Ti]*[Hi]+
[AdditionalUnits]))))))

This returns "13" for this scenario:
[Ti]2 [Hi]8 [AdditionalUnits]5
This should be "21" however the code is adding only 8+5.

This returns "9" for this scenario:
[Ti]10 [Hi]0 [AdditionalUnits]9
This should be "19" however the code is adding only 0+9.

Any help would be greatly appreciated - thanks!

 

[John Spencer]

Assuming that Ti and Hi are never negative, you should be able to
simplify that to the following.

IIF(NZ(Ti,0)= 0 Or Nz(Hi,0) = 0
, Nz(Ti,0) + Nz(HI,0)+ [AdditionalUnits]
, Ti*Hi + AdditionalUnits)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[KARL DEWEY]

Try this ---
UnitLoadTotal: IIf(Nz([Ti],0)=0,Nz([Hi],0) + Nz([AdditionalUnits],0),
IIf(Nz([Hi],0)=0,Nz([Ti],0) + Nz([AdditionalUnits],0),IIf(Nz([Ti],0)>=1 AND
Nz([Hi],0)>=1,Nz([Ti],0)*(Nz([Hi],0)+Nz([AdditionalUnits],0),0))))

 

[jlute]

Sure! Easy for you to say! Geez that IS simplified! And it works great
- thanks!

Assuming that Ti and Hi are never negative, you should be able to
simplify that to the following.

IIF(NZ(Ti,0)= 0 Or Nz(Hi,0) = 0
, Nz(Ti,0) + Nz(HI,0)+ [AdditionalUnits]
, Ti*Hi + AdditionalUnits)

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2008
  Center for Health Program Development and Management
  University of Maryland Baltimore County
'====================================================

Hello all you MVP's and Access enthusiasts!

I have a tricky one that I can't resolve and thought maybe someone
could help!

If [Ti] is "0" then [Hi] + [AdditionalUnits]
If [Hi] is "0" then [Ti] + [AdditionalUnits]
If [Ti] is "1" or more and [Hi] is "1" or more then [Ti]*[Hi]+
[AdditionalUnits]

I came up with this but it's not returning as desired:
UnitLoadTotal: IIf(Nz([Ti],0)<>0,IIf(Nz([Hi],1)<>1,[Hi]+
[AdditionalUnits],IIf(Nz([Ti],1)<>1,IIf(Nz([Hi],0)<>0,[Ti]+
[AdditionalUnits],IIf(Nz([Ti],1)<>1,IIf(Nz([Hi],1)<>1,[Ti]*[Hi]+
[AdditionalUnits]))))))

This returns "13" for this scenario:
[Ti]2 [Hi]8 [AdditionalUnits]5
This should be "21" however the code is adding only 8+5.

This returns "9" for this scenario:
[Ti]10 [Hi]0 [AdditionalUnits]9
This should be "19" however the code is adding only 0+9.

Any help would be greatly appreciated - thanks!- Hide quoted text -

- Show quoted text -

 

[jlute]

Thanks, Karl! I went with John's as he really shaved it down.

Try this ---
UnitLoadTotal: IIf(Nz([Ti],0)=0,Nz([Hi],0) + Nz([AdditionalUnits],0),
IIf(Nz([Hi],0)=0,Nz([Ti],0) + Nz([AdditionalUnits],0),IIf(Nz([Ti],0)>=1 AND
Nz([Hi],0)>=1,Nz([Ti],0)*(Nz([Hi],0)+Nz([AdditionalUnits],0),0))))

--
KARL DEWEY
Build a little - Test a little

Hello all you MVP's and Access enthusiasts!

I have a tricky one that I can't resolve and thought maybe someone
could help!

If [Ti] is "0" then [Hi] + [AdditionalUnits]
If [Hi] is "0" then [Ti] + [AdditionalUnits]
If [Ti] is "1" or more and [Hi] is "1" or more then [Ti]*[Hi]+
[AdditionalUnits]

I came up with this but it's not returning as desired:
UnitLoadTotal: IIf(Nz([Ti],0)<>0,IIf(Nz([Hi],1)<>1,[Hi]+
[AdditionalUnits],IIf(Nz([Ti],1)<>1,IIf(Nz([Hi],0)<>0,[Ti]+
[AdditionalUnits],IIf(Nz([Ti],1)<>1,IIf(Nz([Hi],1)<>1,[Ti]*[Hi]+
[AdditionalUnits]))))))

This returns "13" for this scenario:
[Ti]2 [Hi]8 [AdditionalUnits]5
This should be "21" however the code is adding only 8+5.

This returns "9" for this scenario:
[Ti]10 [Hi]0 [AdditionalUnits]9
This should be "19" however the code is adding only 0+9.

Any help would be greatly appreciated - thanks!- Hide quoted text -

- Show quoted text -