getting records by date

Toran · #1 (**permalink**) April 28th, 2004, 03:32 PM

ok ... i'm going ballistic
i run an Access Database and need to retrieve records withing a specific timeframe i ask for "startDate" and "endDate" and receive it with the following code:

startDate = cDate(request.queryString("startDate"))
endDate = cDate(request.queryString("endDate"))
ID = request.queryString("ID")

Now this is my Select statement and the dates seem not to work ...:

sqlStrDef = "SELECT dh.RefNr, cr.RefNr, Finish FROM DataHead dh, CritRes cr "
sqlStrDef = sqlStrDef & "WHERE dh.RefNr = cr.RefNr "
sqlStrDef = sqlStrDef & "AND DateCompleted >= #" & startDate & "# And DateCompleted <= #" & endDate & "#"
sqlStrDef = sqlStrDef & "AND dh.Division = '"&strSearch&"' OR dh.Team = '"&strSearch& "'OR dh.CaseOfficer ='"&strSearch& "' "
sqlStrDef = sqlStrDef & "ORDER BY cr.RefNr"
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConn
objCommand.CommandText = sqlStrDef
'executing and resetting to Nothing
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute
Set objCommand = Nothing

I have tryed as well retrieving with the following satement:

sqlStrDef = "SELECT dh.RefNr, cr.RefNr, Finish FROM DataHead dh, CritRes cr "
sqlStrDef = sqlStrDef & "WHERE dh.RefNr = cr.RefNr "
sqlStrDef = sqlStrDef & "AND DateCompleted BETWEEN #" & startDate & "# And <= #" & endDate & "#"
sqlStrDef = sqlStrDef & "AND dh.Division = '"&strSearch&"' OR dh.Team = '"&strSearch& "'OR dh.CaseOfficer ='"&strSearch& "' "
sqlStrDef = sqlStrDef & "ORDER BY cr.RefNr"
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConn
objCommand.CommandText = sqlStrDef
'executing and resetting to Nothing
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute
Set objCommand = Nothing

The results are either nothing or all are selected ... but not the records withing the date range.

where do i go wrong???

Any help would be appreciated

... there is always more to learn...

bmains · #2 (**permalink**) April 28th, 2004, 04:32 PM

Could you post the SQL string to the screen and paste that into this message? If I can see an example of the SQL with values you are using that will help.

Thanks,

Brian

Toran · #3 (**permalink**) April 29th, 2004, 05:20 PM

here the entire page so far the funny thing is that if i check only for the start date or only the end date it works...

<%option explicit%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
    <title>ReportPercentResult.asp</title>
</head>
<body>
<div align="center">
<%

dim ID, sqlStrDef, objCommand, objRS, objCommand2, objRS2, startDate, endDate, chkList, strSearch
dim strRef, totalRefCtr, trueCtr, falseCtr

if NOT Request.QueryString("Team")= "" then
    strSearch = Request.QueryString("Team")
end if
if NOT Request.QueryString("Division")= "" then
    strSearch = Request.QueryString("Division")
end if
if NOT Request.QueryString("CaseOfficer")= "" then
    strSearch = Request.QueryString("CaseOfficer")
end if

startDate = cDate(request.queryString("startDate"))
endDate = cDate(request.queryString("endDate"))
ID = request.queryString("ID")

response.write startDate &"<br>"
response.write endDate &"<br>"

sqlStrDef = "SELECT dh.RefNr, cr.RefNr, Finish FROM DataHead dh, CritRes cr "
sqlStrDef = sqlStrDef & "WHERE dh.RefNr = cr.RefNr "
sqlStrDef = sqlStrDef & "AND DateCompleted >= #" & startDate & "# And DateCompleted <= #" & endDate & "#"
sqlStrDef = sqlStrDef & "AND dh.Division = '"&strSearch&"' OR dh.Team = '"&strSearch& "'OR dh.CaseOfficer ='"&strSearch& "' "
sqlStrDef = sqlStrDef & "ORDER BY cr.RefNr"
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConn
objCommand.CommandText = sqlStrDef
'executing and resetting to Nothing
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute
Set objCommand = Nothing

if objRS.eof then
    response.write ""
else
    response.write "<table border='1' bgcolor ='#e0e0e0' cellpadding='5' cellspacing='5'><tr><th>Issue</th><th>Total # of</th>"
    objRS.movefirst
    strRef = objRS("cr.RefNr")
    totalRefCtr = 1
    trueCtr = 0
    falseCtr = 0
    while not objRS.eof
        if objRS("cr.RefNr") = strRef then
            if objRS("Finish") = true then
                trueCtr = trueCtr +1
            end if
            if objRS("Finish") = false then
                falseCtr = falseCtr +1
            end if

            objRS.movenext
        else
            totalRefCtr = totalRefCtr + 1
            objRS.movenext
            strRef = objRS("cr.RefNr")
        end if
    wend
    response.write "<tr><td>Reference Numbers</td><td>" & totalRefCtr & "</td></tr>"
    response.write "<tr><td>Completed Number of C/B/A Numbers</td><td>" & trueCtr & "</td></tr>"
    response.write "<tr><td>Currently Not completed C/B/A Numbers</td><td>" & falseCtr & "</td></tr></table><br><br>"
End If

objRS.close

%>
</dev>
</body>
</html>

any help is appreciated... thanks Brian

... there is always more to learn...

bmains · #4 (**permalink**) April 30th, 2004, 08:09 AM

Hey,

Instead of this:

sqlStrDef = sqlStrDef & "AND DateCompleted BETWEEN #" & startDate & "# And <= #" & endDate & "#"

Try:

sqlStrDef = sqlStrDef & "AND DateCompleted BETWEEN #" & startDate & "# And #" & endDate & "#"

What's different is after the and I removed the "<=", as I think this may be causing problems. Also, could you do a:

Response.Write(sqlStrDef)

after the query and post that. That may help too.

Brian

Toran · #5 (**permalink**) April 30th, 2004, 05:27 PM

once again the code:

<%option explicit%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
    <title>ReportPercentResult.asp</title>
</head>
<body>
<div align="center">
<%

dim ID, sqlStrDef, objCommand, objRS, objCommand2, objRS2, startDate, endDate, chkList, strSearch
dim strRef, totalRefCtr, trueCtr, falseCtr

if NOT Request.QueryString("Team")= "" then
    strSearch = Request.QueryString("Team")
end if
if NOT Request.QueryString("Division")= "" then
    strSearch = Request.QueryString("Division")
end if
if NOT Request.QueryString("CaseOfficer")= "" then
    strSearch = Request.QueryString("CaseOfficer")
end if

startDate = cDate(request.queryString("startDate"))
endDate = cDate(request.queryString("endDate"))
ID = request.queryString("ID")

response.write startDate &"<br>"
response.write endDate &"<br>"

sqlStrDef = "SELECT dh.RefNr, cr.RefNr, Finish FROM DataHead dh, CritRes cr "
sqlStrDef = sqlStrDef & "WHERE dh.RefNr = cr.RefNr "
sqlStrDef = sqlStrDef & "AND DateCompleted BETWEEN #" & startDate & "# And #" & endDate & "#"
sqlStrDef = sqlStrDef & "AND dh.Division = '"&strSearch&"' OR dh.Team = '"&strSearch& "'OR dh.CaseOfficer ='"&strSearch& "' "
sqlStrDef = sqlStrDef & "ORDER BY cr.RefNr"
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConn
objCommand.CommandText = sqlStrDef
'executing and resetting to Nothing
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute
Set objCommand = Nothing

Response.Write(sqlStrDef) & "<br><br><br>"

if objRS.eof then
    response.write ""
else
    response.write "<table border='1' bgcolor ='#e0e0e0' cellpadding='5' cellspacing='5'><tr><th>Issue</th><th>Total # of</th>"
    objRS.movefirst
    strRef = objRS("cr.RefNr")
    totalRefCtr = 1
    trueCtr = 0
    falseCtr = 0
    while not objRS.eof
        if objRS("cr.RefNr") = strRef then
            if objRS("Finish") = true then
                trueCtr = trueCtr +1
            end if
            if objRS("Finish") = false then
                falseCtr = falseCtr +1
            end if

            objRS.movenext
        else
            totalRefCtr = totalRefCtr + 1
            objRS.movenext
            strRef = objRS("cr.RefNr")
        end if
    wend
    response.write "<tr><td>Reference Numbers</td><td>" & totalRefCtr & "</td></tr>"
    response.write "<tr><td>Completed Number of C/B/A Numbers</td><td>" & trueCtr & "</td></tr>"
    response.write "<tr><td>Currently Not completed C/B/A Numbers</td><td>" & falseCtr & "</td></tr></table><br><br>"
End If

objRS.close

%>
</dev>
</body>
</html>

here is the outcome: (including the SQL string)

1/1/2001
1/1/2001
SELECT dh.RefNr, cr.RefNr, Finish FROM DataHead dh, CritRes cr WHERE dh.RefNr = cr.RefNr AND DateCompleted BETWEEN #1/1/2001# And #1/1/2001#AND dh.Division = 'Toga' OR dh.Team = 'Toga'OR dh.CaseOfficer ='Toga' ORDER BY cr.RefNr

it does not make sense ...
in the database the date is set as Date (i had it for some time as text since i did not want to search on it at first and i thought it's not required but that changed).

Issue Total # of
Reference Numbers 1
Completed Number of C/B/A Numbers 9
Currently Not completed C/B/A Numbers 0

it should not find anything since the Database has only 1 entry sometime (in 2003) for test purpose only

... there is always more to learn...

happygv · #6 (**permalink**) April 30th, 2004, 08:15 PM

Toran, Brian was asking you to post the output of this line

Response.Write(sqlStrDef) & "<br><br><br>"

Not the entire code again:)

Also it would be better if you could run the output of the "sqlStrDef" in your Access DB directly and post its status along with any error you get while executing the same in your db directly. That would help you resolve the problem quicker.

HTH
Cheers!

-Vijay G

Toran · #7 (**permalink**) May 1st, 2004, 12:52 PM

got it solved.
the sql string now :

sqlStrDef = "SELECT DataHead.RefNr, CritRes.RefNr,DataHead.Finish, CritRes.AssetNr, CritRes.CritName, CritRes.Status, DataHead.DateCompleted, DataHead.CaseOfficer, DataHead.Team "
sqlStrDef = sqlStrDef & "FROM DataHead INNER JOIN CritRes ON DataHead.RefNr = CritRes.RefNr "
sqlStrDef = sqlStrDef & "WHERE CritRes.Status ='NM' AND DataHead.DateCompleted Between #" &startDate&"# And #"&endDate&"# AND DataHead.CaseOfficer= '"&strSearch&"' OR DataHead.Team = '"&strSearch&"' "
sqlStrDef = sqlStrDef & "AND DataHead.Finish = 'true'"

it's done in the Microsoft Access application and revised for .asp

Vijay and Brian ... Thanks so much for your help and the great tip to run it in Access (never thought about it) DUHHHHH... as i say ... there is ALWAYS more to learn ...
Thanks again

... there is always more to learn...

Need to download code?

Navigation