Solved

Date Range - check against existing dates already entered

Posted on 2004-08-11
4
1,081 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
  • 3
4 Comments
 
LVL 11

Accepted Solution

by:
sciber_dude earned 500 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Code Manager | Snippits 2 66
Reg Exp to extract Url from string asp 12 41
ASP Classic Query that needs to display more information 18 37
JS to redirect to prev page 8 25
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…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

696 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