Solved

Room Availability Check Acc. to Time (MS Access)

Posted on 2010-08-30
4
599 Views
Last Modified: 2012-05-10
Hey

My Table Structure is something like this:

conference_hall_floor
date
timefrom
timeto
username

I want a query where user can check the availability of room according to the time.

I want the user to input Conference Hall, Date, Timefrom and Timeto

If the room is booked, it will show a msgbox showing room is booked by "username", else it will show "It's Available"

Is it possible?

Regards,
Anshu
0
Comment
Question by:Rahul Sehrawat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 85
ID: 33566228
You can use a SELECT statement to check this:

Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT * FROM YourTAble WHERE [Date]=#" & Me.YourDateControl & "# AND [conference_hall_floor]=" & Me.YourConferenceHallControl & " AND ([timefrom] BETWEEN " & Me.YourTimeFromControl & " AND " & Me.YourTimeToControl & ")")

If Not (rst.EOF and rst.BOF) Then
  '/you found a record, so the room isn't available
Else
  '/you didn't find a record - the room is available
End If

You'll need to tweak this, of course, but that should get you started.
0
 

Author Comment

by:Rahul Sehrawat
ID: 33568245
What about TimeToControl?? How will check the conference hall booking end time?
0
 

Accepted Solution

by:
Rahul Sehrawat earned 0 total points
ID: 33583695
Dim tmFrom As Date, tmTo As Date
Dim rst As DAO.Recordset, sql
Dim sCrit As String
tmFrom = TimeSerial(Hour(Me.timefrom), Minute(Me.timefrom), 0)
tmTo = TimeSerial(Hour(Me.timeto), Minute(Me.timeto), 0)

    If DCount("*", "Booking", "conference_hall_floor = '" & Me.conference_hall_floor & "' And [date] = #" & Me.[date] & "#") = 0 Then
        MsgBox Me.conference_hall_floor & " is available on " & Me.[date]
        Exit Sub
    End If
    sql = "SELECT count(*),first([userName]) FROM booking WHERE booking.[date] = #" & Me.[date] & "#"
    sql = sql & " AND booking.conference_hall_floor = '" & Me.conference_hall_floor & "'"
    sql = sql & " And ("
    sql = sql & "(#" & tmFrom & "#"
    sql = sql & " Between timeserial(Hour([booking].[timefrom]),Minute([booking].[timefrom]),0)"
    sql = sql & " And timeserial(Hour([booking].[timeto]),Minute([booking].[timeto]),0)"
    sql = sql & " Or #" & tmTo & "#"
    sql = sql & " Between timeserial(Hour([booking].[timefrom]),Minute([booking].[timefrom]),0)"
    sql = sql & " And timeserial(Hour([booking].[timeto]),Minute([booking].[timeto]),0)"
    sql = sql & ")"
    sql = sql & " Or (timeserial(Hour([booking].[timefrom]),Minute([booking].[timefrom]),0)"
    sql = sql & " Between #" & tmFrom & " # And #" & tmTo & "#)"
    sql = sql & " Or (timeserial(Hour([booking].[timeto]),Minute([booking].[timeto]),0)"
    sql = sql & " Between #" & tmFrom & " # And #" & tmTo & "#)"
    sql = sql & ")"


    Set rst = CurrentDb.OpenRecordset(sql)
        If rst(0) = 0 Then
            MsgBox "Booking Available"
        Else
            MsgBox "Already Booked by " & rst(1)
        End If
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question