Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IFF statement (Access 2000)

    Hey I am trying to make an IFF statement for this reservation system I have, I have these fields for this particular table: Reservation ID (PK), Room #, Check In date, Check in time, Check out Date, Check out Time. I also have a room table that has Room # field, available field and room type. I am trying to create an IFF statement that checks if any rooms of a particular type are available for a certain date period and I am just coming up short. I know its a lengthy if statement and I am just having trouble creating it. If anyone could guide me in the right direction I would appreciate it. Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IFF statement (Access 2000)

    Hi Jordan,

    What is the Available field in the Room table, i.e. what is its type and what is its purpose?

  3. #3
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IFF statement (Access 2000)

    The available field is a yes/no. Its purpose is so i can run a query later on if say i just want to see what rooms are available and what rooms are taken. Its not a necesity it was just how i set it up.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IFF statement (Access 2000)

    I'm going to ignore the Available field then.

    In the following, I haven't taken the Check In Time and Check Out Time into account, since you only mention "available for a certain date period".

    Say that you have a form frmDateSelect with text boxes txtDateFrom and txtDateTo and a combo box cbxRoomType. You want to know if there are rooms of the room type specified in the combo box available for the entire period from txtDateFrom to txtDateTo. You're looking for records whose check out date is before the "from date", or the check out date is after the "to date". The following query returns the available rooms:

    SELECT [tblRoom].[Room#]
    FROM [tblRoom] INNER JOIN [tblThisParticularTable] ON [tblRoom].[Room#] = [tblThisParticularTable].[Room#]
    WHERE [tblRoom].[Room Type] = [Forms]![frmDateSelect]![cbxRoomType] And ([tblThisParticularTable].[Check Out Date] < [Forms]![frmDateSelect]![txtDateFrom] Or [tblThisParticularTable].[Check In Date] > [Forms]![frmDateSelect]![txtDateFrom])

    Save this query as qryAvailableRooms. The expression DCount("*","qryAvailableRooms") is positive if there are rooms available, 0 if there are no rooms available. If you use this as control source of a text box, put = before it.

  5. #5
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IFF statement (Access 2000)

    Ok i will try that out Hans thank you I greatly appreciate it. One question about what you said, If I wanted to incorporate time in and time out there how would I do that. I only ask because if someone checks out on the morning of the 15th, someone can check in the day of the 15th?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IFF statement (Access 2000)

    Do you also want to specify the time from/to which the room should be available, or is a room available on a certain day if check out time is before 12 noon, or check in time is after 12 noon (or some other time)?

  7. #7
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IFF statement (Access 2000)

    We are using sample data for the project so we actually planned to make all the check out times 11:00.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IFF statement (Access 2000)

    Thanks, but that doesn't answer my question. How do you decide if a room is available on a certain day. If one guest checks out at 11 AM and the next would like to check in at 12 noon, you may not have enough time to change the beds etc. Or is that kind of thing irrelevant? In other words, what are the precise criteria for declaring a room available?

  9. #9
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IFF statement (Access 2000)

    Sorry Hans I see what you mean, I was going to have it be the latest checkout of 11 am and the earliest check in be 3 pm

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IFF statement (Access 2000)

    Just replace the > and < in the where clause of the query by 'greater than or equal' (a > followed by an = character) and 'less than equal' (a < followed by an = character). I don't type the combinations in this post because some browsers render them incorrectly.

  11. #11
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IFF statement (Access 2000)

    Ok that makes sense, now hopefully my last question--When it displays how many are empty would it be possible to also display the Room # as well?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IFF statement (Access 2000)

    The query (qryAvailableRooms) returns the room numbers of the available rooms. You can use it as row source for a list box or combo box, or as record source for a continuous form.

  13. #13
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IFF statement (Access 2000)

    OK new issue but related, I figured some issues out, my new problem is that If a reservation is made for a room for ANY date, it wont show up in the query for other dates. I attached a copy of the DB and it works through the form with req button requerying everything.

    I would appreciate any guidance

    Thanks!
    Attached Files Attached Files

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IFF statement (Access 2000)

    Jordan,

    The query had to be more complicated than I first usggested. The join has to be an outer join (to include ALL records from tbRoom, and you have to take care of nul values. Here is the new and hopefully improved SQL:

    SELECT tbRoom.[Room#]
    FROM tbRoom LEFT JOIN tbreservation ON tbRoom.[Room#] = tbreservation.[Room#]
    WHERE (((tbreservation.[Check Out Date])<=[Forms]![frmDateSelect]![txtDateFrom] Or (tbreservation.[Check Out Date]) Is Null) AND ((tbRoom.[Room Type])=[Forms]![frmDateSelect]![cbxRoomType])) OR (((tbRoom.[Room Type])=[Forms]![frmDateSelect]![cbxRoomType]) AND ((tbreservation.[Check In Date])>=[Forms]![frmDateSelect]![txtDateTo] Or (tbreservation.[Check In Date]) Is Null));

    Moreover, you must requery the subform each time you select another room type or date.

    Finally, I don't understand what Count does in the subform. You want a count of the available rooms, not a count per room.

    I have attached the database with some modifications. (I removed objects not necessary for this thread, so don't overwrite your own database!)

    <img src=/w3timages/blueline.gif width=33% height=2>

    Do yourself a favor and
    - Turn off the Name AutoCorrect option in all your databases (Tools | Options..., General tab.)
    - Turn off Subdatasheets in all your tables.
    - Define a primary key in all your lookup tables, e.g. on Customer Type in the custType table.
    - In the future, avoid using # in field names, and don't use spaces in field names and in the names of database objects.
    Attached Files Attached Files

  15. #15
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IFF statement (Access 2000)

    Hans, Another 2 questions.
    1. If i have a form that lists all the reservation with navigation buttons at the bottom is there a way to to create a button that deletes the current record that I am looking at?

    2. And, I wanna create a query that shows all the people who are arriving today, people who are currenly guests and people who are departing today. I know how to create 3 seperate reports but is there a way to create one report that indicates which on the 3 categories they fit into?

    Honestly, sorry to keep bugging you but I am going blind looking at this thing . Thanks again

    jordan

Page 1 of 2 12 LastLast

Posting Permissions

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