Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Unanswered: Access - Can do date filtering?

    I'm currently using Asp with Access as DB. I have a sql select statement which require date filtering. However, I keep on getting the following error:

    Error Type:
    Microsoft JET Database Engine (0x80040E07)
    Data type mismatch in criteria expression.

    My sql statement is something like this:
    " where Date between '#"&fDateFr&"#' and '#"&fDateTo&"#'"

    Can anyone tell me whether access can do date filtering?

  2. #2
    Join Date
    Feb 2004
    Posts
    142

    Re: Access - Can do date filtering?

    Originally posted by hocksoo
    I'm currently using Asp with Access as DB. I have a sql select statement which require date filtering. However, I keep on getting the following error:

    Error Type:
    Microsoft JET Database Engine (0x80040E07)
    Data type mismatch in criteria expression.

    My sql statement is something like this:
    " where Date between '#"&fDateFr&"#' and '#"&fDateTo&"#'"

    Can anyone tell me whether access can do date filtering?
    By using the single quotes you have cast the dates and #s as a string. Remove the single quotes and it should work.
    KC

  3. #3
    Join Date
    Feb 2004
    Posts
    3

    Smile

    Thank you. I have have solve the problem!!!

  4. #4
    Join Date
    Oct 2003
    Posts
    706
    Yeah. The first example constructed:

    date between '#01/01/2003#' ...

    which, because of the presence of the quotes, Access interpreted to be a string literal (which, as it happens, consists of a funky-lookin' mixture of "#"s and "/"s and digits).

    For a date-type literal in Access, the "#"s are the delimeters:

    date between #01/01/2003# ...

    Also, nit-pickin' here: I suggest that you avoid using column-names, like Date, which match SQL reserved-words. Although MS-Access can resolve the ambiguity by enclosing the offending names in square brackets, other SQL dialects may not support this syntax and what you'll be left with is an application that can't readily be "upsized" to a different platform. Use more descriptive titles, like OrderDate.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

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