Input Date Is Between 2 DB Date Records

[rn5a]

A MS-Access DB table has 2 columns - StartTime & EndTime. Though the
data type of both the columns are Date/Time, the records under these 2
columns stores ONLY the TIME part & NOT the DATE part (for e.g.
7:00:00 AM, 2:00:00 PM, 8:35:00 PM etc.).

A Form in a ASP page has 3 dropdown lists. The 1st one is to select an
hour option, the 2nd one to select a minute option (this dropdown list
has only 4 options - 00, 15, 30, 45). The 3rd dropdown list is for
users to select AM or PM.

When users post the Form, how do I find out if the time entered in the
Form by users comes in between the StartTime & EndTime records that is
stored in the Access DB table & show a message to users accordingly?

For e.g. assume that one of the StarTime & EndTime records in the DB
table is, say, 8:00:00 AM & 10:00:00 AM respectively. Using the Form,
a user posts the time as 9:15 AM. Since 9:15 AM comes between 8:00:00
AM & 10:00:00 AM, then the user should be shown a message asking him
to re-enter the time in the Form since the time he posted is between
8:00:00 AM & 10:00:00 AM.

But if a user enters the time in the Form as, say, 2:00 PM, then he
should be allowed to proceed to the next step. Note that if a user
enters the time in the Form as 10:00:00 AM, then also he should be
allowed to proceed to the next step.

Thanks,

RON

 

[Bob Barrows [MVP]]

A MS-Access DB table has 2 columns - StartTime & EndTime. Though the
data type of both the columns are Date/Time, the records under these 2
columns stores ONLY the TIME part & NOT the DATE part (for e.g.
7:00:00 AM, 2:00:00 PM, 8:35:00 PM etc.).

A Form in a ASP page has 3 dropdown lists. The 1st one is to select an
hour option, the 2nd one to select a minute option (this dropdown list
has only 4 options - 00, 15, 30, 45). The 3rd dropdown list is for
users to select AM or PM.

When users post the Form, how do I find out if the time entered in the
Form by users comes in between the StartTime & EndTime records that is
stored in the Access DB table & show a message to users accordingly?

For e.g. assume that one of the StarTime & EndTime records in the DB
table is, say, 8:00:00 AM & 10:00:00 AM respectively. Using the Form,
a user posts the time as 9:15 AM. Since 9:15 AM comes between 8:00:00
AM & 10:00:00 AM, then the user should be shown a message asking him
to re-enter the time in the Form since the time he posted is between
8:00:00 AM & 10:00:00 AM.

But if a user enters the time in the Form as, say, 2:00 PM, then he
should be allowed to proceed to the next step. Note that if a user
enters the time in the Form as 10:00:00 AM, then also he should be
allowed to proceed to the next step.

I think the following will work
<%
timeposted = request.form("time")
on error resume next
timeposted=cdate(timeposted)
if err <> 0 then
'open a db connection using a variable called conn
sql="select count(*) from ... where " & _
"? between StartTime and EndTime"
set cmd=createobject("adodb.command")
cmd.commandtext=sql
cmd.commandtype=1 'adCmdText
set cmd.activeconnection = conn
set rs=cmd.execute(,array(timeposted))
if clng(rs(0).value) > 1 then
response.write "Time slot in use"
else
'continue
end if
else
response.write "Invalid Time entered"
end if

 

[Bob Barrows [MVP]]

if clng(rs(0).value) > 1 then

I meant to type:

if clng(rs(0).value) > 0 then