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

    Unanswered: IIf statement in query...help needed!

    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:

    ARRIVALDATE
    [Enter date]>=Date()
    OR
    Is Null
    OR
    Not [Enter date]

    CAPACITY
    >=[Enter capacity]
    OR
    >=[Enter capacity]
    OR
    >=[Enter capacity]


    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.

    Thanks

    Grace
    Last edited by graceadair; 05-31-04 at 01:50.

  2. #2
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Quote Originally Posted by graceadair
    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?
    Try this:

    Available: IIf(([ArrivalDate]<>[DateParameter]) And IsNull([ArrivalDate]) And IsNull([ArrivalTime]),"Yes","No")
    Last edited by Cosmos75; 06-10-04 at 12:38.
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

Posting Permissions

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