Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004

    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:

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

    >=[Enter capacity]
    >=[Enter capacity]
    >=[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.



  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    Table schema please.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    May 2004


    Here it is along with SQL:

    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]));

Posting Permissions

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