iif

[tcgaines]

I have a field called drvQty and a field posType. If posType is "CusShip", the drvQty refers to the quantity of customer shipments. If posType is "IC", then drvQty refers to the quantity of Inter Company shipments. If neither of these are true, drvQty refers to OpenShippers. I want to write a query that will declare drvQty AS CusShipQuantity and drvQty AS ICQty and drvQty AS OpenShip depending on the posType field.

This is what I have, and it doesnt work:
SELECT tblDrive.drvPosType, iif(tblDrive.drvPosType="CusShip", tblDrive.drvQty AS CusShip, iif(tblDrive.PosType="IC", tblDrive.drvQty AS IC, tblDrive.drvQty AS OpenShip))
FROM tblDrive

Thank you for any advice.

 

[FoFa]

Isolate each column as it's own IIF first.
Supply and ELSE for each column (I used zero in the example below).

iif(tblDrive.drvPosType="CusShip", tblDrive.drvQty,0) AS CusShip

 

[Matt Greatorex]

Try using normal If statements to work out which conditions apply, then DLookup to find the actual value you need.

e.g.

If A Then

If C Then
DLookup (condition one)
Else
DLookup (condition two)
End If

ElseIf B
If C Then
DLookup (condition three)
Else
DLookup (condition four)
End If
End If

 

[tcgaines]

Isolate each column as it's own IIF first.
Supply and ELSE for each column (I used zero in the example below).

iif(tblDrive.drvPosType="CusShip", tblDrive.drvQty,0) AS CusShip

perfect. thank you both for your suggestions.