ASP reservation system with Access DB - Date Range.

I have a reservation system currently built in ASP and Access. Now im trying to build a function to check availablilty of suites on the attempt of a new booking.

For example:
Lets say someone has a reservation for May12th - 13th. If someone else comes along and tries to book May 10th - 20th it will work and overlap the other reservation.

The date range function "BETWEEN" in SQL isnt checking the individual dates of the ranges, simply the range as a whole, so A date range that is smaller and within than the one being inquired about will cause the program to overlap suite bookings.

Is there an efficient way to carry this out?

Thanks in advance.

CRSQL = "SELECT * FROM h_bookinfo WHERE (room_id=" & roomid & ") AND ((#" & checkin &"#  BETWEEN checkin_date and checkout_date AND #" & checkout &"#  BETWEEN checkin_date and checkout_date) OR (#" & checkin &"# <= checkin_date AND #" & checkout &"#  >= checkout_date))"
 
If CRLookup.EOF or CRLookup.BOF Then
		'response.write("Room is not booked")
		checkroom = false
		Else 
		'response.write("Room is booked")
		checkroom = true
		End If

Select allOpen in new window