Nested Count Iif Statement

[Topflite66]

I am working on a nested Iif statement that counts vehicles that meet a 3 separate criteria. Below is my Expression in a query:

CountIncoming: Count(IIf([tbl_OrderDetails]![VIN]=Null,Null,IIf([tbl_OrderDetails]![OnLot]=Yes,Null,IIf([tbl_OrderDetails]![DateSold] Is Not Null, Null,1))))

The purpose of this expression is to first check to see if the vehicle has a VIN, if it does not then count 0 and stop. If it does have a VIN go to the 2nd Iif statement which checks to see if the On Lot check box is checked. If the check box is checked then count 0 and stop. If the check box is not checked then go to the 3rd Iif statement which checks to see if there is a date in the DateSold field. If there is a date in the DateSold field then count 0. If the vehicle has a VIN, the On Lot Check Box is not checked and there is no date in the Date Sold field then count 1. Otherwise I should get a 0 count.

Everything is working well except I have one vehicle that was not delivered to us. So it did not have a VIN, was not considered On Lot and did not have a date sold. Because the unit was not assigned a VIN I thought that it should be counted as 0 and then the expression would stop. However, because it does not have a Date Sold it is being counted as 1. So it seems like the only requirement that really matters in the statement is whether the vehicle has a date sold or not.

Any help would be greatly appreciated. Thank you

[ranman256]

if you need to nest IIFs then dont bother
instead either create a lookup query
or
write a custom function.

much easier to read/decode.

[June7]

Cannot compare anything to Null. Review http://allenbrowne.com/casu-12.html

Consider:

CountIncoming: Count(IIf([tbl_OrderDetails]![VIN] Is Null Or [tbl_OrderDetails]![OnLot]=True Or Not [tbl_OrderDetails]![DateSold] Is Null, Null,1))

[Topflite66]

Perfect. Thank you for your help June7. Also thank you for the info on Null Errors.