Understanding IFF in Query

[Lissa]

Hi y'all -
I inherited messy pharmacy reports that are written in MS Access but retreive data from a MS SQL server db.

I am having trouble understanding what a nasty IIF statement is doing in a query for one of the reports. The IIF statement is used to determine the value of a field called TotalAcquisitionCostByLot.


TOTALAcquisitionCostByLot: IIf(Inventory!Type>"D",[WholeDoses]*Inventory!AcquisitionCost,(IIf(InventoryControl!InventoryControlRef>0,(IIf(Inventory!DispenseWholeUnits<0,[WholeDoses]*InventoryControl!UnitCost,[TotalDoses]*InventoryControl!UnitCost)),(IIf(Inventory!DispenseWholeUnits,[WholeDoses]*[AcquisitionCost],[TotalDoses]*[AcquisitionCost])))))

I am trying to rewrite this as an if-else statement so it's a bit easier for me to understand and but no matter how I write it - I keep coming up with different values.
Here are my NOTES from what I think this is doing...
Inventory Type can be a D, E, F, I, N, O, P, R, S or T
and Dispense whole units is stored as a bit and is either 1 or 0 in the database.

If Inventory Type <> D then
whole doses * inventory acquisition cost
else if inventory control ref > 0
if inventory dispense whole units = true then
whole units * inventory control unit cost
else
total doses * inventory control unit cost

if inventory dispense whole units = true then
whole doses * inventory acquisition cost
else
total doses * inventory acquisition cost


Does this look like what's going on in the IFF?

Any help would be much appreciated. I'm :banghead:


Thanks

 

[jdraw]

This part of your understanding is incorrect or mistyped
If Inventory Type <> D then

The original IIF has IIf(Inventory!Type>"D", which means Greater than NOT (not equal)

 

[plog]

Additionally you wrote this:

if inventory dispense whole units = true then

Whereas the original has:

Inventory!DispenseWholeU nits<0

Which means its not treating it as a boolean (true/false) as you did, but as a number. However, immediately after that section the original has this:

IIf(Inventory!Dispen seWholeUnits,

Where it is treated as a boolean.

Whoever wrote the original needs their typing fingers broken. Send me a name and address.

 

[Lissa]

@jdraw - oops that was a typo

@plog - My thoughts exactly about the typing fingers!! Especially when I'm the one stuck maintaining that crap! I'm trying to port the reports over to Crytal Reports and DispenseWholeUnits can only be treated as a boolean. No matter how I rewrite it - I can't get the same results.
I was hoping to get a better understanding of what the original creator was trying to do in MS Access but guess a better solution might be to go back to the users and try to recapture the original requirement.



Here it is written for Crystal Reports.

IIf({Inventory.Type} > "D",
{@Whole Doses}*{Inventory.AcquisitionCost},
(IIf({InventoryControl.InventoryControlRef}>0,
(IIf({Inventory.DispenseWholeUnits}<> True,
{@Whole Doses}*{InventoryControl.UnitCost},
{@Total Doses}*{InventoryControl.UnitCost})),

(IIf({Inventory.DispenseWholeUnits},
{@Whole Doses}*{Inventory.AcquisitionCost},
{@Total Doses}*{Inventory.AcquisitionCost})))))

 

[vbaInet]

You should use a function instead. Writing several IIF functions to suit your needs is too cumbersome. Create the function and call it in your query.