More time stuff

schedcheck = "Select Sched_Date,Sched_Start,Sched_End FROM Schedule WHERE
Sched_Date = #"& strSched_Date &"# AND (Sched_Start BETWEEN # "&
strSched_Start &" # AND # "& dateAdd("n",-1,strSched_End) &" # OR Sched_End
BETWEEN # "& dateAdd("n",1,strSched_Start) &"# AND # "& strSched_End &"#) "
set rs=conn.execute(schedcheck)
IF rs.EOF Then
Add EVENT
Else
Response.Write "THERE IS A CONFLICT!"

Im trying to check for conflict in a schedule application.


The above works great for a New Event start time or end time which lands
between an existing event but if the new event is schedule inside an
existing event, I get no Conflict.

For instance, say I have an existing event from 8:00 AM thru 12:00 PM.
Trying to schedule an event from 7:00 AM to 10:00 AM (which overlaps) it
gives me a conflict which is what I want.

However, If I schedule an event from 9:00 AM to 10:00 AM it allows the
update and doesnt give me a conflict.

Any ideas what I need to add to get this to work?

Thanks!

Re: More time stuff

Which type of field are Sched_Start & Sched_End ?
Show us exemple of how strSched_start & strSched_End are formatted.
Gérard

Re: More time stuff

Targa wrote:
> schedcheck = "Select Sched_Date,Sched_Start,Sched_End FROM Schedule
> WHERE Sched_Date = #"& strSched_Date &"# AND (Sched_Start BETWEEN # "&
> strSched_Start &" # AND # "& dateAdd("n",-1,strSched_End) &" # OR
> Sched_End BETWEEN # "& dateAdd("n",1,strSched_Start) &"# AND # "&
> strSched_End &"#) "

Could you do a response.write of this so we can see the actual sql statement
instead of the vbscript statements that are supposed to result in a sql
statement? Don't get me wrong, we usually need to see both.

> set rs=conn.execute(schedcheck)
> IF rs.EOF Then
> Add EVENT
> Else
> Response.Write "THERE IS A CONFLICT!"
>
> Im trying to check for conflict in a schedule application.
>
>
> The above works great for a New Event start time or end time which
> lands between an existing event but if the new event is schedule
> inside an existing event, I get no Conflict.
>
> For instance, say I have an existing event from 8:00 AM thru 12:00 PM.
> Trying to schedule an event from 7:00 AM to 10:00 AM (which overlaps)
> it gives me a conflict which is what I want.
>
> However, If I schedule an event from 9:00 AM to 10:00 AM it allows the
> update and doesnt give me a conflict.
>

As always, the best way to get help with a query is to show us sample data
in tabular format followed by the result you wish the query to return (also
in tabular format). When showing us one of your attempts, as in this case,
show us what the attempt returns in tabular format.

Bob Barrows

PS. You should look into using saved parameter queries. See these:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=ukS%246S%247CHA.2464%40TK2MSFTNGP11.phx.gbl -
shows select statement only

http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFT NGP10.phx.gbl&oe=UTF-8&output=gplain -
good

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: More time stuff

The times are stored as Medium Time format.
response.write schedcheck:

Select Sched_Date,Sched_Start,Sched_End,Sched_OrderNum FROM Schedule WHERE
Sched_Date = #12/20/2004# AND (Sched_Start BETWEEN # 3:00 AM # AND # 4:59:00
AM # OR Sched_End BETWEEN # 3:01:00 AM# AND # 5:00 AM#)

Thanks.


"Bob Barrows [MVP]" wrote in message
news:%23%23C7oxP5EHA.1596@tk2msftngp13.phx.gbl...
> Targa wrote:
>> schedcheck = "Select Sched_Date,Sched_Start,Sched_End FROM Schedule
>> WHERE Sched_Date = #"& strSched_Date &"# AND (Sched_Start BETWEEN # "&
>> strSched_Start &" # AND # "& dateAdd("n",-1,strSched_End) &" # OR
>> Sched_End BETWEEN # "& dateAdd("n",1,strSched_Start) &"# AND # "&
>> strSched_End &"#) "
>
> Could you do a response.write of this so we can see the actual sql
> statement instead of the vbscript statements that are supposed to result
> in a sql statement? Don't get me wrong, we usually need to see both.
>
>> set rs=conn.execute(schedcheck)
>> IF rs.EOF Then
>> Add EVENT
>> Else
>> Response.Write "THERE IS A CONFLICT!"
>>
>> Im trying to check for conflict in a schedule application.
>>
>>
>> The above works great for a New Event start time or end time which
>> lands between an existing event but if the new event is schedule
>> inside an existing event, I get no Conflict.
>>
>> For instance, say I have an existing event from 8:00 AM thru 12:00 PM.
>> Trying to schedule an event from 7:00 AM to 10:00 AM (which overlaps)
>> it gives me a conflict which is what I want.
>>
>> However, If I schedule an event from 9:00 AM to 10:00 AM it allows the
>> update and doesnt give me a conflict.
>>
>
> As always, the best way to get help with a query is to show us sample data
> in tabular format followed by the result you wish the query to return
> (also in tabular format). When showing us one of your attempts, as in this
> case, show us what the attempt returns in tabular format.
>
> Bob Barrows
>
> PS. You should look into using saved parameter queries. See these:
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=ukS%246S%247CHA.2464%40TK2MSFTNGP11.phx.gbl -
> shows select statement only
>
> http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFT NGP10.phx.gbl&oe=UTF-8&output=gplain -
> good
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>

Re: More time stuff

Targa wrote:
> The times are stored as Medium Time format.

No, they are not. That may be how they are getting displayed, but that is
not how they are being stored. Jet stores datetimes as numbers of type
Double, with the integer portion representing the number of days since the
seed date, and the decimal portion representing te time of day (.0 =
midnight, .5 = noon). The Format property simply determines how the values
will be displayed to the user in any Access interface (external
applications, such as ASP, need to supply their own formatting).

> response.write schedcheck:
>
> Select Sched_Date,Sched_Start,Sched_End,Sched_OrderNum FROM Schedule
> WHERE Sched_Date = #12/20/2004# AND (Sched_Start BETWEEN # 3:00 AM # AND #
> 4:59:00 AM # OR Sched_End BETWEEN # 3:01:00 AM# AND # 5:00 AM#)
>

Do you get the correct results when you copy this statement into the SQL
View of the Access Query Builder and test it (tip: always use the query
builder to develop and test your queries, even if you intend to continue
making the mistake of using dynamic sql)?

If not, we need to see some sample data (in tabular format - just show the
relevant fields)

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"