Access date range query

[lrmac93]

i need a query to check two dates on a form (a date range) against the date ranges already in a table for conflictions.
Basically i need to return records where any of the dates in the date range on the form conflict beteen any of the dates in the database.
eg.
if the form date range was [DATE FROM] 02/01/02 and [DATE TO] 02/10/02
and there was a record in the table already for [DATE FROM] 02/03/02 and [DATE TO] 02/12/02 then the record would be returned because the dates conflict.
I expected there to be a function for conflicting date ranges already in access so if there is one i need it and if there isnt how can it be done?

 

[KenReay]

Hi

How about:

Dim Db As Database
Dim Rs As DAO.Recordset
Dim strSQL as String
'
Set DB = CurrentDb()
strSQL = "SELECT * FROM tblYourTable WHERE datDate BETWEEN #" & Format(FromDate,"mm/dd/yy&quot & "# AND #" & Format(ToDate,"mm/dd/yy&quot & "#;"
Set RS = db.OpenRecordset(strSQL)
If Rs.RecordCount > 0 Then
mshbox "Conflict"
...etc
End if
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[lrmac93]

That works if either the FROM date or TO date im checking fall between a FROM date or TO date already in the database but i dont think it pulls the records where the dates im checking start before the FROM date and end after the TO date, even though they still conflict. I should explain the whole thing:
i have a database that records they booking out of laptops to personnel. when they ask me for a laptop i go into the database and record their details including the dates they want the laptop FROM and TO. i want the database to check the records for conflictions with those dates.
eg. (using UK date format)
If someone wanted the laptop FROM 20/06/02 TO 25/06/02
and that laptop was already booked FROM 18/06/02 TO 28/06/02
that record needs to be returned. At the moment it is not being returned, even though the dates conflict, becuase neither the FROM date or TO fall BETWEEN the other dates already in the database.
somehow i need to check a range of dates for conflictions against another range of dates. im out of ideas.

 

[KenReay]

Hi

How about

strSQL = "SELECT * FROM tblYourTable WHERE #" & Format(FromDate,"mm/dd/yy&quot & "# BETWEEN datFDate AND datTDate OR #" & Format(ToDate,"mm/dd/yy&quot & "# BETWEEN datFDate AND datTDate;"


This assuming you laptop table has the hire period as datFDate to datTDate

Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use