Finding records between given date and time

[s_sridhar]

Hi all, I'm working on conference room booking software with MS-Access as the back end. I need to retrieve the records which doesn't clash with the already booked dates.

I have a table called conference_room_status which has the fields

Conference_Room_Name | BookedPerson | DateFrom | DateTo | TimeFrom | TimeTo

where DateFrom and DateTo (date format) is the starting and ending date of the reservation... and the person may need the conference room only between TimeFrom and TimeTo (time format)...

Any ideas of how to write an sql query to retrieve these records will be greatly appreciated

[dmarino1]

Hi all, I'm working on conference room booking software with MS-Access as the back end. I need to retrieve the records which doesn't clash with the already booked dates.

I have a table called conference_room_status which has the fields

Conference_Room_Name | BookedPerson | DateFrom | DateTo | TimeFrom | TimeTo

where DateFrom and DateTo (date format) is the starting and ending date of the reservation... and the person may need the conference room only between TimeFrom and TimeTo (time format)...

Any ideas of how to write an sql query to retrieve these records will be greatly appreciated

Do you know how to use queries in Access? you can actually create a form in Access to use a query that will focus on date inputs.

[ans025]

You need to do a query. You can either do it in design view or with SQL. If you don't know how to write SQL design view can help you with that to a certain extent. Also there are a lot of resources you can search for that show you SQL

[Stylish]

Does Access have a DateTime data type? I would use that if it does (under the assumption that you can book a conference room for more than one day).

Here is an example, just using the DateTo/From stuff (this assumes that DateFrom is always < DateTo):

select *
from conferences
where DateTo < now() -- booking ended before now 
or DateFrom > now()  -- booking does not begin until a future date

I am not sure if 'now()' is an actual function in Access. Replace with a specific Date or whatever Access uses to get the current time.

[dmarino1]

now() does work in access. in your query, for criteria, you can put that there,
Example:

Between #1/1/2009# And (Now())

the above will give you the start date to todays date, so if you are looking for things inbetween that are open, closed or whatever else you are trying to do.