results page not filtering on date

Microsoft Access backend database.

I am trying to filter a recordeset on several criteria, including dates.
The page works but the data is never filtered on date. Here is the code
in the results page

strWhere="WHERE"
strOrderBy = " ORDER BY"
if (request.form("chkSorting")="date") then
strOrderBy = strOrderBy & " Journal.Start DESC"
end if

if (request.form("chkSorting")="type") then
strOrderBy = strOrderBy & " Journal.Type ASC"
end if

if (request.Form("lngNameID")<> "All") then
varName = Cstr(request.Form("lngNameID"))
strWhere=strWhere & " Users.EntryID = '" & varName & "'" & " AND "
end if

varContactType=""
if (request.Form("txtType")<>"All") then
varContactType = request.Form("txtType")
strWhere=strWhere & " Journal.Type = '" & varContactType & "'" & "
AND "
end if

varKeyword=""
if (request.Form("txtKeyword")<>"All") then
varKeyword = request.Form("txtKeyword")
strWhere=strWhere & " Journal.Body Like '%" & varKeyword & "%'" & "
AND "
end if

if IsDate(request.Form("dteStart")) then
dteStart=FormatDateTime(request.Form("dteStart"),vbShortDate )
strWhere=strWhere & " Journal.Start >= #" & dteStart & "# AND "
Else
dteStart=#07/01/2004#
end if

if IsDate(request.Form("dteEnd")) then
dteEnd=FormatDateTime(request.Form("dteEnd"),vbShortdate)
strWhere=strWhere & " Journal.Start <= #" & dteEnd & "# AND "
Else
dteEnd=#31/10/2004#
end if

if right(strWhere,5)=" AND " then
strWhere=Left(strWhere,Len(strWhere)-5)
end if
if strWhere="WHERE" then strWhere = ""
%>

I can't workout why the dates never get filtered. I search for a dates in
~August 2004 but I will get every row returned?

Any help appreciated.

Ray Keattch

RE: results page not filtering on date

Raymond,

At first glance what I suspect is happening is that the "IF" tests for your
date fields are always failing so the where clause never gets updated with
the filter criteria.

You might put some Response.write statements inside the IF tests to find
out which branch your code is actually taking.

For Example:
If IsDate(...) Then
RESPONSE.WRITE "IF CONDITION PASSED"
...
Else
RESPONSE.WRITE "IF CONDITION FAILED"
...
End If


You might also put a RESPONSE.WRITE to show your final SQL string before
processing it.

Example:
after: if strWhere="WHERE" then strWhere = ""
Response.Write "WHERE: " & strWhere

HTH,
John






"Raymond Keattch" wrote:

> Microsoft Access backend database.
>
> I am trying to filter a recordeset on several criteria, including dates.
> The page works but the data is never filtered on date. Here is the code
> in the results page
>
> strWhere="WHERE"
> strOrderBy = " ORDER BY"
> if (request.form("chkSorting")="date") then
> strOrderBy = strOrderBy & " Journal.Start DESC"
> end if
>
> if (request.form("chkSorting")="type") then
> strOrderBy = strOrderBy & " Journal.Type ASC"
> end if
>
> if (request.Form("lngNameID")<> "All") then
> varName = Cstr(request.Form("lngNameID"))
> strWhere=strWhere & " Users.EntryID = '" & varName & "'" & " AND "
> end if
>
> varContactType=""
> if (request.Form("txtType")<>"All") then
> varContactType = request.Form("txtType")
> strWhere=strWhere & " Journal.Type = '" & varContactType & "'" & "
> AND "
> end if
>
> varKeyword=""
> if (request.Form("txtKeyword")<>"All") then
> varKeyword = request.Form("txtKeyword")
> strWhere=strWhere & " Journal.Body Like '%" & varKeyword & "%'" & "
> AND "
> end if
>
> if IsDate(request.Form("dteStart")) then
> dteStart=FormatDateTime(request.Form("dteStart"),vbShortDate )
> strWhere=strWhere & " Journal.Start >= #" & dteStart & "# AND "
> Else
> dteStart=#07/01/2004#
> end if
>
> if IsDate(request.Form("dteEnd")) then
> dteEnd=FormatDateTime(request.Form("dteEnd"),vbShortdate)
> strWhere=strWhere & " Journal.Start <= #" & dteEnd & "# AND "
> Else
> dteEnd=#31/10/2004#
> end if
>
> if right(strWhere,5)=" AND " then
> strWhere=Left(strWhere,Len(strWhere)-5)
> end if
> if strWhere="WHERE" then strWhere = ""
> %>
>
> I can't workout why the dates never get filtered. I search for a dates in
> ~August 2004 but I will get every row returned?
>
> Any help appreciated.
>
> Ray Keattch
>

Re: results page not filtering on date

Raymond Keattch wrote:
> I have built a where condition in my results page to filter results by
> several criteria including dates. I get results from the search fine,
> but the dates are never filtered. This is what I have in the results
> page.
>
> The backend database is Microsoft Access.
>
> if IsDate(request.Form("dteStart")) then
> dteStart=FormatDateTime(request.Form("dteStart"),vbShortDate )
> strWhere=strWhere & " Journal.Start >= #" & dteStart & "# AND "
> Else
> dteStart=#07/01/2004#
> end if
>
> if IsDate(request.Form("dteEnd")) then
> dteEnd=FormatDateTime(request.Form("dteEnd"),vbShortdate)
> strWhere=strWhere & " Journal.Start <= #" & dteEnd & "# AND "
> Else
> dteEnd=#31/10/2004#

Bad. Access requires date literals to be supplied in US format (mm/dd/yyyy)
or ISO (yyyy-mm-dd or yyyy/mm/dd). ISO is recommended since it is less
ambiguous

> end if
>
> Why do I get all dates returned?
>
> Raymond Keattch.

Look at the content of strWhere. You'll see.

Here is some information about using dates:
http://www.aspfaq.com/show.asp?id=2313 vbscript
http://www.aspfaq.com/show.asp?id=2040 help with dates
http://www.aspfaq.com/show.asp?id=2260 dd/mm/yyy confusion

Bob Barrows
--
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: results page not filtering on date

"=?Utf-8?B?Sm9obiBCZXNjaGxlcg==?="
wrote in news:A38CE4AC-7602-43FF-A09A-B848CB79A3BC@microsoft.com:

> You might also put a RESPONSE.WRITE to show your final SQL string before
> processing it.
>
> Example:
> after: if strWhere="WHERE" then strWhere = ""
> Response.Write "WHERE: " & strWhere
>

Thanks for the response. I have just tried your suggestion and I get the
following...

WHERE Users.EntryID =
'000000001A447390AA6611CD9BC800AA002FC45A09007C67AF08CF6ED31 18CE800508B0FFE
FA000000095DE100007C67AF08CF6ED3118CE800508B0FFEFA0000000971 640000' AND
Journal.Start >= #4/10/04# AND Journal.Start <= #19/10/04#

so the WHERE statement is being built properly but the data returned is for
the correct user, but all dates????

Ray Keattch

Re: results page not filtering on date

> Journal.Start >= #4/10/04# AND Journal.Start <= #19/10/04#

Try using real dates. What is 4/10/04? April 10, 2004? October 4, 2004?
Who knows? 19/10/04 could be loosely interpreted in even more ways. Why
make your database guess? ALWAYS, ALWAYS, ALWAYS pass YYYY-MM-DD to Access.
Then there is no confusion, errors or implicit conversion...

--
http://www.aspfaq.com/
(Reverse address to reply.)

Re: results page not filtering on date

"Aaron [SQL Server MVP]" wrote in
news:uU5rufgtEHA.2668@TK2MSFTNGP12.phx.gbl:

>> Journal.Start >= #4/10/04# AND Journal.Start <= #19/10/04#
>
> Try using real dates. What is 4/10/04? April 10, 2004? October 4,
> 2004? Who knows? 19/10/04 could be loosely interpreted in even more
> ways. Why make your database guess? ALWAYS, ALWAYS, ALWAYS pass
> YYYY-MM-DD to Access. Then there is no confusion, errors or implicit
> conversion...
>

Thanks for the pointer. I had a good read of the ASP FAQS and got the page
working with all your suggestions.

Thanks all!

Ray Keattch