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 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.