Unanswered: Help urgently needed - IIf statements & Null in query
I have a fairly complicated problem with executing an IIF statement in a query. Hopefully somebody will be able to help me with this. I've posted this once before but I received no response, so I'm hoping somebody will take pity on me and offer a solution!
I have a query within my restaurant database that should return the details of all tables that:
a) have the seating capacity specified in the parameter >=[Enter capacity];
b) have reservations on the date specified in the parameter [Enter date]>=Date();
c) do not have reservations at all - that is, the ArrivalDate field has the value Is Null;
d) have reservations, but not on the date specified in the parameter (that is, they are "available" for the parameter date)
I achieved this by setting the criteria (in Query design view) as the following:
Not [Enter date]
Trouble is, that when the query returns the details about tables reserved on a date other than that which is entered in the parameter, it returns them containing the ArrivalDate and ArrivalTime values as defined; I need to run some kind of IIf statement, that will do the following:
If the value in the ArrivalDate field is not the same as the date entered in the parameter, then the ArrivalDate, ArrivalTime and PartyName fields will appear blank.
Additionally, I have the following calculated field:
Available: IIf([ArrivalDate] Is Null,IIf([ArrivalTime] Is Null,"Yes","No"),"No")
How could I co-ordinate this with the above question; that is, setting it to Available: Yes if the value in the ArrivalDate field is not the same as the value entered in the date parameter?
I hope I've explained this properly. I would appreciate any and all help.
SELECT tblTables.TableNo, tblReservations.ArrivalDate, tblTables.Capacity, tblTables.Location, tblReservations.ArrivalTime, tblReservations.PartyName, tblReservations.PartyContactNo, IIf(([ArrivalDate]<>[Forms]![srchAddSearchTables]![DateNeeded]) Or IsNull([ArrivalDate]) Or IsNull([ArrivalTime]),"Yes","No") AS Available
FROM tblTables LEFT JOIN tblReservations ON tblTables.TableNo = tblReservations.TableNo
WHERE (((tblReservations.ArrivalDate)=[Forms]![srchAddSearchTables]![DateNeeded]) AND ((tblTables.Capacity)>=[Forms]![srchAddSearchTables]![CapacityNeeded])) OR (((tblReservations.ArrivalDate) Is Null) AND ((tblTables.Capacity)>=[Forms]![srchAddSearchTables]![CapacityNeeded]));