Unanswered: Urgent - Query probs - returning all fields in report?
I've created a restaurant database, as many of you would undoubtedly be aware , and I need a way to run my Search for Available tables criteria in quite a specific way.
I need a query to run and return a list of ALL table numbers currently existing within the restaurant, including the ArrivalDate and ArrivalTime fields; and return this list whether or not there are values in these fields. Even better would be a way to add one of those extra calculated fields, with a value akin to:
Available: ([ArrivalDate] value is null And [ArrivalTime] value is null)
(as in, the Available field will somehow contain the value Yes if the above fields are null, otherwise it is No)
...so that I can still view all table records, and easily determine which are available and which are not.
Asking even more, I am hoping your solution would not involve a great deal of additional coding, as the key to this assignment is simplicity (unfortunately). Still, I'd be happy to hear it anyway, just preferably it would be a simple approach.
Ok - to include all tables irrespective of whether or not there is an entry in the reservations table you need to double click on the join in the query window between the two tables and select the option that starts "Include ALL records from tblTables and..." (if you are interested, compare the SQL before you do this and after - you'll probably see "INNER JOIN" change to "RIGHT JOIN" or "LEFT JOIN").
Then, you need to enter Or is Null to your arrival date criteria.
For your expression, try:
Available: IIf([ArrivalDate] Is Null,IIf([ArrivalTime] Is Null,"Yes","No"),"No")
OK, the Available: IIf....thing worked perfectly, but the editing of the join type thing isn't working so well...I've included a screen shot of what I get. I selected the Second option, and when that didn't work (with it still only returning fields with values in them) I tried the third one, to no avail. According to my test data, two tables should show up as the results of the query (if I input the parameter Capacity as 4 and the ArrivalDate as 19/5/04); Tables 2 and 7 (with Table 2 reserved at 9:00PM, and Table 7 unreserved), but so far, only Table 2 shows up in the query results list.