Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Room Availability Check Acc. to Time (MS Access)

Posted on 2010-08-30
4
Medium Priority
?
606 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

636 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