Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    80

    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.

    Thanks very much.
    Attached Thumbnails Attached Thumbnails queryqu.gif  

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hello again Grace

    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")

    Hope this helps
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2004
    Posts
    80

    Sounds good

    Thanks heaps ...it sounds good. I will definitely try it.

    Have a good day, until next time,

    Grace

  4. #4
    Join Date
    May 2004
    Posts
    80

    Ummm...half worked, half didn't

    Hi again,

    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.

    Any further suggestions? Thanks.

    Grace
    Attached Thumbnails Attached Thumbnails queryprob.gif  

  5. #5
    Join Date
    May 2004
    Posts
    80

    Oops...

    Whoops, stupid moi.

    I didn't read your post properly and missed the 'Or Null' thingy.



    I really am silly sometimes.

    Sorry to bother you, thanks again for your help and if I should encounter any more problems with this, I'll let you know.

    Grace

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •