Topflite66
New member
- Local time
- Today, 14:19
- Joined
- Feb 27, 2018
- Messages
- 2
I am working on a nested Iif statement that counts vehicles that need to meet 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
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