?
Solved

Date Range - check against existing dates already entered

Posted on 2004-08-11
4
Medium Priority
?
1,092 Views
Last Modified: 2008-01-09
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(itemdate))
     sql = sql & "('" & fixdate(newDate) & "' between itemdate AND DateAdd(day,numnights,itemdate)) 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
0
Comment
Question by:fgict
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 11

Accepted Solution

by:
sciber_dude earned 2000 total points
ID: 11777079
> However the user is still allowed enter i.e 24/08/2004 for 7 nights and overlap item.id=3

Why is this so? what is the logic that the user can enter 24/8/2004 and not any other dates? If you can give me the answer for this, i will try to work this in.

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 RsDates = Server.CreateObject("ADODB.Recordset")
      
            sqlDates = "Select * from items where motorid='" & motorid & "'"
            RsDates.open sql, db, 3,1

            While not (RsDates.EOF or RsDates.BOF)
                  sql = sql & "(itemdate > #" & RsDates("itemdate") & "#) AND (itemdate < #" & DateAdd ("d", RsDates("numnights"), RsDates("itemdate")) & "#)) OR "
            Wend

            RsDates.close
            set RsDates=nothing

            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

            Set objRS = Server.CreateObject("ADODB.Recordset")

            objRS.open sql, db, 3,1
            response.write sql & "<br>"

            if not objRS.EOF Then
                   errormessage=errormessage & "You have already choosen a Holiday within this date range!"
            End if
            response.write errormessage & "<br>"

            objRS.close
            set objRS=nothing
      end if                        ' << you were missing this.
end if


:) SD
0
 
LVL 11

Expert Comment

by:sciber_dude
ID: 11777091
Btw, if you are not using MS Access Database, change the # (pound sign) to a ' (single quote) in this line

sql = sql & "(itemdate > #" & RsDates("itemdate") & "#) AND (itemdate < #" & DateAdd ("d", RsDates("numnights"), RsDates("itemdate")) & "#)) OR "
0
 
LVL 1

Author Comment

by:fgict
ID: 11782513
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 :)
0
 
LVL 11

Expert Comment

by:sciber_dude
ID: 11790004
Did u try the code i gave you?

Make sure you replace the "#" with a single quote.

:) SD
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

762 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