Solved

Room Availability Check Acc. to Time (MS Access)

Posted on 2010-08-30
4
592 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
4 Comments
 
LVL 84
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

776 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