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

[dutiano]

I'm not sure that this will work but try to use the vbscript "datediff"
function

--
dutiano
------------------------------------------------------------------------
Posted via http://www.codecomments.com
------------------------------------------------------------------------

[Bob Barrows [MVP]]

rn**@rediffmail.com wrote:

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

--
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.

[Bob Barrows [MVP]]

Bob Barrows [MVP] wrote:

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

I meant to type:

if clng(rs(0).value) 0 then
--
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.