?
Solved

Room Availability Check Acc. to Time (MS Access)

Posted on 2010-08-30
4
Medium Priority
?
607 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
  • 2
3 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

864 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