fgict
asked on
Date Range - check against existing dates already entered
Hi all
adding a record to items table, need to check that the item added does not overlap any date ranges already entered.
(See table records below)
At the moment I am not allowing the user enter any date i.e. 12/08/2004 for 7 nights
However the user is still allowed enter i.e 24/08/2004 for 7 nights and overlap item.id=3
How can i check that the user does not enter any date range already created??
*note -> fixdate() function converts date from (10/08/2004 to 10/Aug/2004) format
- items table -
id motorid itemdate numnights
1 2 10/08/2004 7
2 2 17/08/2004 7
3 2 27/08/2004 7
if itemdate="" then
errormessage=errormessage & "You must specify a Date.<br>"
else
'Check to see if itemdate already exists when adding.
if task="add" or task="update" then
Set objRS = Server.CreateObject("ADODB .Recordset ")
sql = "("
For i=1 TO cInt(numnights)
newDate = DateAdd("d",i,fixdate(item date))
sql = sql & "('" & fixdate(newDate) & "' between itemdate AND DateAdd(day,numnights,item date)) OR "
Next
sql = LEFT(sql,Len(sql)-3) & ")"
if sql <> "" Then
sql = "Select * From items WHERE motorid='" & motorid & "' AND " & sql
Else
sql = "Select * From items WHERE motorid='" & motorid & "'"
End if
objRS.open sql, db, 3,1
response.write sql
if not objRS.EOF Then
errormessage=errormessage & "You have already choosen a Holiday within this date range!"
End if
objRS.close
set objRS=nothing
end if
adding a record to items table, need to check that the item added does not overlap any date ranges already entered.
(See table records below)
At the moment I am not allowing the user enter any date i.e. 12/08/2004 for 7 nights
However the user is still allowed enter i.e 24/08/2004 for 7 nights and overlap item.id=3
How can i check that the user does not enter any date range already created??
*note -> fixdate() function converts date from (10/08/2004 to 10/Aug/2004) format
- items table -
id motorid itemdate numnights
1 2 10/08/2004 7
2 2 17/08/2004 7
3 2 27/08/2004 7
if itemdate="" then
errormessage=errormessage & "You must specify a Date.<br>"
else
'Check to see if itemdate already exists when adding.
if task="add" or task="update" then
Set objRS = Server.CreateObject("ADODB
sql = "("
For i=1 TO cInt(numnights)
newDate = DateAdd("d",i,fixdate(item
sql = sql & "('" & fixdate(newDate) & "' between itemdate AND DateAdd(day,numnights,item
Next
sql = LEFT(sql,Len(sql)-3) & ")"
if sql <> "" Then
sql = "Select * From items WHERE motorid='" & motorid & "' AND " & sql
Else
sql = "Select * From items WHERE motorid='" & motorid & "'"
End if
objRS.open sql, db, 3,1
response.write sql
if not objRS.EOF Then
errormessage=errormessage & "You have already choosen a Holiday within this date range!"
End if
objRS.close
set objRS=nothing
end if
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi SD
thanks for your response, i want to disallow the user i.e entering 24/08/2004 for 7 nights and overlap item.id=3
The user could enter 24/08/2004 for 3 nights but could not overlap a date range already inputted i.e id=3 motorid=2 itemdate=27/08/2004 numnightd=7
Using SQL Server database
hope this explains :)
thanks for your response, i want to disallow the user i.e entering 24/08/2004 for 7 nights and overlap item.id=3
The user could enter 24/08/2004 for 3 nights but could not overlap a date range already inputted i.e id=3 motorid=2 itemdate=27/08/2004 numnightd=7
Using SQL Server database
hope this explains :)
Did u try the code i gave you?
Make sure you replace the "#" with a single quote.
:) SD
Make sure you replace the "#" with a single quote.
:) SD
sql = sql & "(itemdate > #" & RsDates("itemdate") & "#) AND (itemdate < #" & DateAdd ("d", RsDates("numnights"), RsDates("itemdate")) & "#)) OR "