Professional Web Applications Themes

results page not filtering on date - ASP Database

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

  1. #1

    Default 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"),v bShortDate)
    strWhere=strWhere & " Journal.Start >= #" & dteStart & "# AND "
    Else
    dteStart=#07/01/2004#
    end if

    if IsDate(request.Form("dteEnd")) then
    dteEnd=FormatDateTime(request.Form("dteEnd"),vbSho rtdate)
    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
    Raymond Guest

  2. #2

    Default 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:
     
    John Guest

  3. #3

    Default Re: results page not filtering on date

    Raymond Keattch wrote: 

    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
     

    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.


    Bob Guest

  4. #4

    Default RE: results page not filtering on date

    "=?Utf-8?B?Sm9obiBCZXNjaGxlcg==?=" <microsoft.com>
    wrote in news:com:
     

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

    WHERE Users.EntryID =
    '000000001A447390AA6611CD9BC800AA002FC45A09007C67A F08CF6ED3118CE800508B0FFE
    FA000000095DE100007C67AF08CF6ED3118CE800508B0FFEFA 0000000971640000' 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
    Raymond Guest

  5. #5

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


    Aaron Guest

  6. #6

    Default Re: results page not filtering on date

    "Aaron [SQL Server MVP]" <noraa> wrote in
    news:phx.gbl:
     
    >
    > 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...
    >[/ref]

    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
    Raymond Guest

Similar Threads

  1. Filtering dataGrid results
    By Kwooda in forum Macromedia Flex General Discussion
    Replies: 1
    Last Post: May 6th, 04:19 AM
  2. filtering by Date in Recordset
    By cdlcruz in forum Dreamweaver AppDev
    Replies: 2
    Last Post: May 12th, 11:48 PM
  3. filtering a second rs with the filtered results of a first rs
    By Zachariah Crow in forum Dreamweaver AppDev
    Replies: 3
    Last Post: May 10th, 07:24 PM
  4. Filtering query results
    By DuLaus in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: April 7th, 08:30 PM
  5. Filtering by today's date
    By benkayuk in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 5th, 01:38 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139