Results 1 to 3 of 3

Thread: Date filtering

  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Unanswered: 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
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    The syntax is incorrect.

    SELECT tblTableName.fldName
    FROM tblTableName
    WHERE (((tblTableName.fldName) Between #1/1/2004# And #2/28/2004#));

    If you have hardcoded the dates or

    SELECT tblTableName.fldName
    FROM tblTableName
    WHERE (((tblTableName.fldName) Between [forms]![frmName]![txtStartDate] And [forms]![frmName]![txtEndDate]));

    if you take the dates from text boxes on form.

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by DJN
    The syntax is incorrect.

    SELECT tblTableName.fldName
    FROM tblTableName
    WHERE (((tblTableName.fldName) Between #1/1/2004# And #2/28/2004#));

    If you have hardcoded the dates or

    SELECT tblTableName.fldName
    FROM tblTableName
    WHERE (((tblTableName.fldName) Between [forms]![frmName]![txtStartDate] And [forms]![frmName]![txtEndDate]));

    if you take the dates from text boxes on form.
    And this part is incorrect to (UNLESS this query is built in the QBE ...):

    WHERE (((tblTableName.fldName) Between [forms]![frmName]![txtStartDate] And [forms]![frmName]![txtEndDate]));

    It should be:

    WHERE (((tblTableName.fldName) Between " & [forms]![frmName]![txtStartDate] & " And " & [forms]![frmName]![txtEndDate] & "));"

Posting Permissions

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