Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ASP reservation system with Access DB - Date Range.

Posted on 2009-05-15
2
Medium Priority
?
479 Views
Last Modified: 2012-05-07
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

Open in new window

0
Comment
Question by:nomar2
  • 2
2 Comments
 
LVL 44

Accepted Solution

by:
GRayL earned 200 total points
ID: 24398303
I'll leave it to you to get the syntax right but it goes like this:

(checkin_date Not Between checkin AND checkout) OR (checkout_date  Not Between checkin and checkout)
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24495079
Thanks, glad to help.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

916 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