Help with SELECT Statement(Date/Time)

Ive got a scheduling application using an Access db. Scheduling works fine
but Im trying to implement a function to prevent scheduling conflicts. Im
not sure if this is the way to go about it but below is what I have so far
and it's not working. It schedules the event no matter what.
What's wrong with this statement? or is there a better way to do this?

Thanks!

'***GET DATE/TIME SELECTIONS
strSched_Date = request.form("Sched_Date")
strSched_Start = request.form("Sched_Start")
strSched_End = request.form("Sched_End")

conn.Open myconnect
'***DETERMINE IF THERE IS A CONFLICT
schedcheck = "Select Sched_Date,Sched_Start,Sched_End FROM Schedule WHERE
Sched_Date = " & strSched_Date & " AND Sched_Start BETWEEN " &
strSched_Start & " AND " & strSched_End & ""
set rs=conn.execute(schedcheck)

'***If no matches
If rs.EOF Then

mySQL = "INSERT INTO Schedule
(Sched_First,Sched_Last,Sched_Cust,sched_Address,Sched_Date, Sched_Start,Sched_End)"
mySQL = mySQL & " VALUES ( '" & strSched_First & "', '" & strSched_Last &
"', '" & strSched_Cust & "', '" & strsched_Address & "', '" & strSched_Date
& "', '" & strSched_Start & "', '" & strSched_End & "')"
Conn.execute(mySQL)

Else
Response.Write "THERE IS A CONFLICT!"
End If

'Close stuff

Re: Help with SELECT Statement(Date/Time)

First your date must be in American format mm-dd-yyyy and in ##

like mydate = #12-25-2004# (Christmas)

see www.aspFaq.com

Re: Help with SELECT Statement(Date/Time)

Targa wrote:
> Ive got a scheduling application using an Access db. Scheduling works
> fine but Im trying to implement a function to prevent scheduling
> conflicts. Im not sure if this is the way to go about it but below is
> what I have so far and it's not working. It schedules the event no
> matter what.
> What's wrong with this statement? or is there a better way to do this?
>
> Thanks!
>
> '***GET DATE/TIME SELECTIONS
> strSched_Date = request.form("Sched_Date")
> strSched_Start = request.form("Sched_Start")
> strSched_End = request.form("Sched_End")
>
> conn.Open myconnect
> '***DETERMINE IF THERE IS A CONFLICT
> schedcheck = "Select Sched_Date,Sched_Start,Sched_End FROM Schedule
> WHERE Sched_Date = " & strSched_Date & " AND Sched_Start BETWEEN " &
> strSched_Start & " AND " & strSched_End & ""
> set rs=conn.execute(schedcheck)
>
> '***If no matches
> If rs.EOF Then
>
> mySQL = "INSERT INTO Schedule
>
(Sched_First,Sched_Last,Sched_Cust,sched_Address,Sched_Date, Sched_Start,Sche
d_End)"
> mySQL = mySQL & " VALUES ( '" & strSched_First & "', '" &
> strSched_Last & "', '" & strSched_Cust & "', '" & strsched_Address &
> "', '" & strSched_Date & "', '" & strSched_Start & "', '" &
> strSched_End & "')" Conn.execute(mySQL)
>
> Else
> Response.Write "THERE IS A CONFLICT!"
> End If
>
> 'Close stuff
http://www.aspfaq.com/show.asp?id=2313 vbscript
http://www.aspfaq.com/show.asp?id=2040 help with dates
http://www.aspfaq.com/show.asp?id=2260 dd/mm/yyy confusion
http://www.aspfaq.com/show.asp?id=2023 date delimiters

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Help with SELECT Statement(Date/Time)

> First your date must be in American format mm-dd-yyyy

Not necessarily, depending on regional settings, language, localization of
Office, etc. This could fail in the UK, for example, if you use 12-17-2004
(since it will interpret the 12th day of the 17th month, and fail).

Better recommendation is to use #yyyy-mm-dd#, then there is no ambiguity in
any form...

--
http://www.aspfaq.com/
(Reverse address to reply.)

Re: Help with SELECT Statement(Date/Time)

It works if I just use the date but when I try to match the time, thats when
I have problems.

strSched_Date = "12/17/2004"
strSched_Start = "3:00 PM"
strSched_End = "5:00 PM"

I have a existing scheduled event on 12/17/2004 at 2:30 PM to 4:30 PM.

schedcheck = "Select * FROM Schedule WHERE Sched_Date = #12/17/2004# AND
Sched_Start BETWEEN & #strSched_Start# & AND & #strSched_End# & "

When I try scheduling another event on 12/17/2004 at 3:00 PM , it should
give me a conflict but it doesnt and just adds the record.
Do my time fields need ## around them too? Do I have the & in the wrong
place? Am I missing a ' or "?

Thanks.




"Gérard Leclercq" wrote in message
news:4NFwd.2946$Xo2.203795@phobos.telenet-ops.be...
>
> First your date must be in American format mm-dd-yyyy and in ##
>
> like mydate = #12-25-2004# (Christmas)
>
> see www.aspFaq.com
>
>

Re: Help with SELECT Statement(Date/Time)

Got it.
WHERE Sched_Date = #"& strSched_Date &"# AND Sched_Start BETWEEN # "&
strSched_Start &"# AND # "&strSched_End&" # "


"Targa" wrote in message
news:OQRLDeH5EHA.2600@TK2MSFTNGP09.phx.gbl...
> It works if I just use the date but when I try to match the time, thats
> when I have problems.
>
> strSched_Date = "12/17/2004"
> strSched_Start = "3:00 PM"
> strSched_End = "5:00 PM"
>
> I have a existing scheduled event on 12/17/2004 at 2:30 PM to 4:30 PM.
>
> schedcheck = "Select * FROM Schedule WHERE Sched_Date = #12/17/2004# AND
> Sched_Start BETWEEN & #strSched_Start# & AND & #strSched_End# & "
>
> When I try scheduling another event on 12/17/2004 at 3:00 PM , it should
> give me a conflict but it doesnt and just adds the record.
> Do my time fields need ## around them too? Do I have the & in the wrong
> place? Am I missing a ' or "?
>
> Thanks.
>
>
>
>
> "Gérard Leclercq" wrote in message
> news:4NFwd.2946$Xo2.203795@phobos.telenet-ops.be...
>>
>> First your date must be in American format mm-dd-yyyy and in ##
>>
>> like mydate = #12-25-2004# (Christmas)
>>
>> see www.aspFaq.com
>>
>>
>
>