[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Date Range - check against existing dates already entered

Posted on 2004-08-11
4
Medium Priority
?
1,094 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

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…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

649 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