# Thread: Need to decipher a calculation that uses IFF in Access ...

1. Registered User
Join Date
Sep 2015
Posts
1

## Unanswered: Need to decipher a calculation that uses IFF in Access ...

I'm NOT very familiar w/ Access and am having trouble figuring out exactly what this calculation is doing.
The column is called "Months Inventory" and is calculated using the following:

[qty_on_hand]/IIf([LastOforig_qty]=0,1,[LastOforig_qty])

I mean, obviously it's dividing the row result from qty_on_hand by whatever the IFF is returning, but I can't figure out WHAT the IFF is returning (or specifically, HOW it's getting there) ...

Any suggestions would be helpful. Thanks!

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
suggestion
#2 set a watch / breakpoint on the code and examine the value of the code in the immediate window usign the debugger

or failing that
IIF is a boolean expression where
iif(<logical expression>, <action if true>, <action if false>)
so your IIF tests if LastOforig_qty is zero, if so used 1 as the divisor, if its not 0 then it uses LastOforig_qty

it might be safer to re express this to
Code:
`[qty_on_hand]/IIf([LastOforig_qty] <= 0, 1, [LastOforig_qty])`
and depending on where this is used you may also need to cater for NULL values
Code:
`[qty_on_hand]/IIf([nz(LastOforig_qty,0)] <= 0, 1, [LastOforig_qty])`
the boolean expression can be a simple (as in this case a single term) or complex as you want (many tersm using and / or and so on
you can compund IIF statements to replciate the logic of a case statement

eg
Code:
```myvalue = nz(myvalue,0) 'force myvalue to have a valid number, in this case 0
iif(myvalue > 100, 'hundreds', iif(myvalue > 10, 'tens', iif (myvalue > 0, 'ones', iif (myvalue = 0, 'zero', 'negative'))))```
but compound IIF's can be a pig to set up, a pig to debug and so on

Join Date
Feb 2004
Location
New Zealand
Posts
1,482
Why not turn it into a function

in module

Code:
```function Workitout(v1,v2,v3)
'
'
'