Mental gymnastics over last day of rental

[Gina Maylone]

Hello! Happy Holidays from the northland!

I have a rental database, in which I am trying to find the last location a piece of equipment was at and indicate in the inventory list whether it is in or out. But because equipment gets rented and returned often, my end date will not come up null. I first have a query that pulls the max start date:

SELECT DISTINCT Order.SerialNum, Max(Order.StartDate) AS MaxOfStartDate, Order.Equipment
FROM [Order]
GROUP BY Order.SerialNum, Order.Equipment;

And then another query using the first one to grab additional information (Account name, patient name etc).

SELECT Inventory.Equipment, Inventory.SerialNum, Account.AcctName, Order.PatientName, LastStartDate.MaxOfStartDate, Order.EndDate, IIf([maxofstartdate] Is Not Null And [enddate]=Null,"out","in") AS Status
FROM LastStartDate INNER JOIN (Account INNER JOIN ([Order] INNER JOIN Inventory ON Order.SerialNum = Inventory.SerialNum) ON Account.AcctNum = Order.AcctNum) ON LastStartDate.SerialNum = Inventory.SerialNum
GROUP BY Inventory.Equipment, Inventory.SerialNum, Account.AcctName, Order.PatientName, LastStartDate.MaxOfStartDate, Order.EndDate, IIf([maxofstartdate] Is Not Null And [enddate]=Null,"out","in");

What am I forgetting? (seems like more and more everyday!)

[pbaldy]

You say your end date will not be Null, but that's what you're testing for. You can't test for Null with =; try the IsNull() function.

[Gina Maylone]

Thank you pbaldy. I have tried that as well

Status: IIf(not isnull([maxofstartdate] And isnull([order.enddate],"out","in")

but I get a wrong number of arguments error.

[Gina Maylone]

I got it!!!! With this:

Status: IIf([maxofstartdate] Is Not Null And [order.enddate] Is Null,"out","in")

[June7]

The IsNull() usage would work if you had parens correct.

Status: IIf(not isnull([maxofstartdate]) And isnull([order.enddate]),"out","in")