Report the wrong dates ranges.

[hughesmi]

Hi all. My bellow script pulls a date range and counts how many questions have been asked for this date range. The problem I have is the report pullls out the wrong date range.

For example. If I had selected the "4 April" to "4 April" it seems to report back from "3 April" to "5 April"

There seems to a problem with date calculations, where I do no know.


<!--#INCLUDE FILE="global_variables.inc"-->

<%
Session.LCID = 2057
Response.Expires = 60
Response.Expiresabsolute = Now() - 1
Response.AddHeader "pragma","no-cache"
Response.AddHeader "cache-control","private"
Response.CacheControl = "no-cache"

if request.form("startdate") = "" then
response.write("<center><h3>Count Questions.</h3></center>")
response.write("<form name=""theform"" action=""count.asp"" method=""post"">")
response.write("Count the Number of Questions Asled From a Date Range?")
response.write("<br>")
response.write("<br>")
response.write("<b>From: </b>")
response.write("<select name=""startdate"">")

' Show 15 Days Behind Todays Date
dtStart = dateadd("d",-15,date)
for i = 1 to 30
if date = dateadd("d",i,dtStart) then
response.write("<option style=""color:blue"" selected value=""" & month(dateadd("d",i-1,dtStart)) & "/" & day(dateadd("d",i-1,dtStart)) & "/" & year(dateadd("d",i+1,dtStart)) & """>" & WeekdayName(Weekday(dateadd("d",i,dtStart))) & " " & day(dateadd("d",i,dtStart)) & " " & monthname(month(dateadd("d",i,dtStart))) & "</option>")
else
response.write("<option value=""" & month(dateadd("d",i-1,dtStart)) & "/" & day(dateadd("d",i-1,dtStart)) & "/" & year(dateadd("d",i-1,dtStart)) & """>" & WeekdayName(Weekday(dateadd("d",i,dtStart))) & " " & day(dateadd("d",i,dtStart)) & " " & monthname(month(dateadd("d",i,dtStart))) & "</option>")
end if
next

response.write("</select>")

response.write("<b> To: </b>")
response.write("<select name=""enddate"">")

for i = 1 to 30
if date = dateadd("d",i,dtStart) then
response.write("<option style=""color:blue"" selected value=""" & month(dateadd("d",i+1,dtStart)) & "/" & day(dateadd("d",i+1,dtStart)) & "/" & year(dateadd("d",i+1,dtStart)) & """>" & WeekdayName(Weekday(dateadd("d",i,dtStart))) & " " & day(dateadd("d",i,dtStart)) & " " & monthname(month(dateadd("d",i,dtStart))) & "</option>")
else
response.write("<option value=""" & month(dateadd("d",i+1,dtStart)) & "/" & day(dateadd("d",i+1,dtStart)) & "/" & year(dateadd("d",i+1,dtStart)) & """>" & WeekdayName(Weekday(dateadd("d",i,dtStart))) & " " & day(dateadd("d",i,dtStart)) & " " & monthname(month(dateadd("d",i,dtStart))) & "</option>")
end if

next

response.write("</select> <input type=""submit"" value=""go""></form></center>")
else

Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open DataSource1

SQL_query = "SELECT * FROM Questions WHERE DateTime_Asked BETWEEN #"& request.form("startdate") &"# AND #"& request.form("enddate") &"# ORDER BY DateTime_Asked"
Set rsNames = MyConn.Execute(SQL_query)


' Count how many records exist on that above date range
DIM iRecordCount
iRecordCount = 0
DO WHILE NOT rsNames.EOF
iRecordCount = iRecordCount + 1
rsNames.MoveNext
Loop

' Display result
Response.Write ("There are <b>" & iRecordCount & "</b> Asked Between the:")
Response.Write ("<br>")
Response.Write ("<br>")
Response.Write ("From ") & request.form("startdate")
Response.Write "<br>"
Response.Write ("To ") & request.form("enddate")

'Reset server objects
rsNames.Close
Set rsNames = Nothing
Set adoCon = Nothing
End IF
%>

[fractalvibes]

try

SELECT * FROM Questions WHERE DateTime_Asked >= startdate
and Date_Time_ASked <= enddate

and see what range you get. If it still returns dates out of the range, then
response.write SQL_query
response.end

and see what dates it is pulling from the form.

fv

[hughesmi]

I can't see how this would work? How would the sql know how to pull the form data? This the reason why I had the request.form in my sql statment.

I did try your example but it chuck a pure fit!!!!! ;)


Thanks but no joy! :o

[fractalvibes]

that was just supposed to be "psuedocode" - use the >= and <= arguments
instead of the between with your current sql using the request.form stuff as it is.

fv

[hughesmi]

Oh.. yeah. I should have worked that out for myself.

Anyways.... I have worked the problem out. It was the dates that was the problem. It works fine with this solution.


dtStart = dateadd("d",-15,date)
for i = 1 to 30
if date = dateadd("d",i,dtStart) then
response.write("<option style=""color:blue"" selected value=""" & month(dateadd("d",i,dtStart)) & "/" & day(dateadd("d",i,dtStart)) & "/" & year(dateadd("d",i,dtStart)) & """>" & WeekdayName(Weekday(dateadd("d",i,dtStart))) & " " & day(dateadd("d",i,dtStart)) & " " & monthname(month(dateadd("d",i,dtStart))) & "</option>")
else
response.write("<option value=""" & month(dateadd("d",i,dtStart)) & "/" & day(dateadd("d",i,dtStart)) & "/" & year(dateadd("d",i,dtStart)) & """>" & WeekdayName(Weekday(dateadd("d",i,dtStart))) & " " & day(dateadd("d",i,dtStart)) & " " & monthname(month(dateadd("d",i,dtStart))) & "</option>")
end if
next

response.write("</select>")

response.write("<b> To: </b>")
response.write("<select name=""enddate"">")

for i = 1 to 30
if date = dateadd("d",i,dtStart) then
response.write("<option style=""color:blue"" selected value=""" & month(dateadd("d",i,dtStart)) & "/" & day(dateadd("d",i,dtStart)) & "/" & year(dateadd("d",i,dtStart)) & """>" & WeekdayName(Weekday(dateadd("d",i,dtStart))) & " " & day(dateadd("d",i,dtStart)) & " " & monthname(month(dateadd("d",i,dtStart))) & "</option>")
else
response.write("<option value=""" & month(dateadd("d",i,dtStart)) & "/" & day(dateadd("d",i,dtStart)) & "/" & year(dateadd("d",i,dtStart)) & """>" & WeekdayName(Weekday(dateadd("d",i,dtStart))) & " " & day(dateadd("d",i,dtStart)) & " " & monthname(month(dateadd("d",i,dtStart))) & "</option>")
end if
next
response.write("</select> <input type=""submit"" value=""go""></form><br><br><center><a href=""form.asp"">back to form</a></center>")
else