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
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