restrict insert based on range of dates

[kredd]

ok, see if you can follow me

have a reservation form that asks for start and ending dates. both are seperate textfields and database fields. i need to be able to restrict users from submitting starting and ending dates if these dates already exist in database. that part is easy enough...what i can't figure out is how to restrict starting/ending dates that fall on the interim dates of existing reservations...example:

user1: start date to end date - 7/1/2005 to 7/10/2005
user2: start date to end date - 7/2/2005 to 7/9/2005

user2 should fail since his days fall inbetween user1's reservations. make sense??

i've tried using between or not between - no success. even tried subtracting the start date from end date and then create an array using a for/next loop to build the interim dates by adding the num of days back to the start date as it loops thru. kinda seemed like i was onto something but hit a dead end and started confusing myself !!

thanks in advance, kelly

[wmif]

insert (column1,column2) values (value1,value2) where not (value1 between column1 and column2) and not (value2 between column1 and column2)

off hand thats what i used before. it should work. if not, post back your exact query and ill look around to find the query that i used before.

[kredd]

thanks, the code makes sense to me but how would i implement it? how do return an error to the user if the values can't be inserted? i've been using COUNT(ID) AS BLAH to see if a record exists and firing an if/then statement based on its existence or non-existence...

if BLAH = 1 then "tell user to fix something", if BLAH = 0 then insert the reservation and all is well.

here is what i've done with your code:

sql666="insert into marquee (date_requested,end_date) values ('" & date_requested & "','" & end_date & "')"
sql666= sql666 + "where not ('" & date_requested & "' between date_requested AND end_date) and not ('" & end_date & "' between date_requested AND end_date)"

[kredd]

oh yeah, the important part....i submitted starting and ending dates that fall between two existing dates and unfortunatley it successfully inserted the reservation. so something's not working right...probably my modification

[wmif]

uhhhh -- nevermind. wasnt paying enough attention earlier. inserts dont take the where clause. use that between setup in a select statement that will return no rows if you are clear to insert or it will return rows if its not clear. handle the error/routing from there.

[kredd]

i didn't think they did, just thot it was something i hadn't heard of before.

be a lot cooler if it did thnks

[kredd]

just finished the project...the 'between code' fit perfectly into my existing checking statements and worked great...thanks for the help