Input Date Is Between 2 DB Date Records
am 13.05.2007 19:18:46 von 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
Re: Input Date Is Between 2 DB Date Records
am 14.05.2007 15:26:54 von reb01501
rn5a@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.
Re: Input Date Is Between 2 DB Date Records
am 14.05.2007 15:57:39 von reb01501
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.