Nested Iif Function issue (1 Viewer)

Topflite66

New member
Local time
Today, 05:00
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
 

Mark_

Longboard on the internet
Local time
Today, 05:00
Joined
Sep 12, 2017
Messages
2,111
To clarify, how are you storing the following fields?
[tbl_OrderDetails].[VIN]
[tbl_OrderDetails].[OnLot]
[tbl_OrderDetails].[DateSold]

As you are specifically testing for NULL, if you are checking a type that cannot hold NULL that would give you issues.
 

MarkK

bit cruncher
Local time
Today, 05:00
Joined
Mar 17, 2004
Messages
8,178
Also, nothing ever equals Null--not even Null--so this expression...
Code:
tbl_OrderDetails.VIN = Null
...will always return Null. To check if VIN is null in SQL use "Is Null" and in VBA use the IsNull() function.
hth
Mark
 

Mark_

Longboard on the internet
Local time
Today, 05:00
Joined
Sep 12, 2017
Messages
2,111
Thanks Mark... I missed that.
 

Topflite66

New member
Local time
Today, 05:00
Joined
Feb 27, 2018
Messages
2
Thank you for your responses. I appreciate the comments. I was able to get a solution from a user on another Access site which works perfect.

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

Always a learning process. Thank you.
 

MarkK

bit cruncher
Local time
Today, 05:00
Joined
Mar 17, 2004
Messages
8,178
Well, then you don't need the IIF(). You are just summing the result of the boolean expression itself...
Code:
CountIncoming: -Sum(VIN Is Null Or OnLot Or Not DateSold Is Null)
Mark
 

Users who are viewing this thread

Top Bottom