restrict insert based on range of dates
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
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.
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)"
Last edited by kredd; 06-01-2005 at 06:12 PM.
Reason: your modified code added
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
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.
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
just finished the project...the 'between code' fit perfectly into my existing checking statements and worked great...thanks for the help
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)