Date range query

  • Thread starter Thread starter SimonDeLikeable
  • Start date Start date
S

SimonDeLikeable

Hi
I have a table called Bookings, which has a FromDate and ToDate. I want
a query to show what bookings I have on a particular date. For example,
which Bookings I have for the 26th February?
What kind of SQL statement would I need?

Thanks
Diarmuid
 
Hi
I have a table called Bookings, which has a FromDate and ToDate. I want
a query to show what bookings I have on a particular date. For example,
which Bookings I have for the 26th February?
What kind of SQL statement would I need?

Thanks
Diarmuid
Click to expand...

SELECT <whatever>
FROM Bookings
WHERE [FromDate] >= [Enter date:]
AND [ToDate] <= [Enter date:]
 
Try

Select * from tblBookings
Where (([FromDate]<=[Enter Date]) AND ([ToDate]>=[Enter Date]));

Kelvin
 
Hi Diarmuid,

Thank you for posting in the community.

I understand that you'd like to use SQL statement in Access to query which Bookings you
had for some day. Thanks for Kelvin and John's kind help and sample code. One thing I
need to mentioned is Jet SQL has tiny difference with T-SQL in date expression in the
Where clause.

We should use the sharp mark (#) instead of single quotation marks (') to wrap the date and
the sample should be like this (Northwind database, Orders Table):
SELECT * FROM [Orders] WHERE [OrderDate] = #26-02-1998#

In your scenario, the statement can say (assume year 2004):

SELECT <whatever>
FROM Bookings
WHERE [FromDate] >= #26-02-2004#
AND [ToDate] <= #26-02-2004#


Does this answer your question Diarmuid? Please feel free to let us know if this help solves
your problem. If there is anything more we can do to assist you, please feel free to post it in
the group.

Best regards,

Billy Yao
Microsoft Online Support
 
Thanks guys, but unfortunately none of these answers will work.
For example, say the Booking had FromDate = 25/02/04 and ToDate = 27/02/04
It would fail under the criteria given - ToDate <= 26/02/04 would fail.
Any other suggestions?
Diarmuid
 
Thanks guys, but unfortunately none of these answers will work.
For example, say the Booking had FromDate = 25/02/04 and ToDate = 27/02/04
It would fail under the criteria given - ToDate <= 26/02/04 would fail.
Any other suggestions?
Click to expand...

Oops. Got the inequalities backwards! Of course it should be

SELECT <whatever>
FROM Bookings
WHERE [FromDate] <= [Enter date:]
AND [ToDate] >= [Enter date:]
 
Diarmuid

Thanks for further information!

To query teh records with "FromDate = 25/02/04 and ToDate = 27/02/04", we can use the
following statement to select the Booking records with the given criteria " ToDate <= #
26/02/04# " :

-----------------------------------------------
Select * From Booking
Where [FromDate] <= #26/02/04#
AND [ToDate] >= #26/02/04#
-----------------------------------------------

If there is anything more I can do to assist you, please feel free to post it in the group. We
are here to be of assistance!

Best regards,

Billy Yao
Microsoft Online Support
 
Back
Top