Datatype mismatch errors in Access parameterized Date query

This article was previously published under Q175258
This article has been archived. It is offered "as is" and will no longer be updated.
SYMPTOMS
When using the Query Designer to execute a parameterized query based upon aDateTime field against an Access data source, you will receive thefollowing error:
ODBC Error: 22005: [Microsoft][ODBC Microsoft Access 97 Driver] Data type mismatch in criteria expression.
CAUSE
The conditions that cause this error are as follows:
  • Using an Access data source. This behavior does not occur against a SQL data source.
  • Search criteria on a Date/Time Data type field.
  • User enters the value of the search criteria in the form of 'mm/dd/yy'
RESOLUTION
There are two situations where you need to apply a workaround to thisbehavior:
  • When executing this type of query in the Query Designer, such as in testing the query before saving the Active Server Pages (ASP) script, it is best to hard code a test date in the criteria field in the "Grid Pane" and then run the query (see step 7 under Steps to Reproduce Behavior" in the MORE INFORMATION section below).
  • When executing this type of query in the ASP script, you must change the code that the Design-Time Control outputs to one of the below formats:

    Hard Coded Date:
          cmdTemp.CommandText = "SELECT ClimbingTopSales.*,      ClimbingTopSales.ProductIntroductionDate FROM ClimbingTopSales WHERE      (((ClimbingTopSales.ProductIntroductionDate) =#4/23/96#))"						
    Parameterized Date:
          cmdTemp.CommandText = "SELECT ClimbingTopSales.*,      ClimbingTopSales.ProductIntroductionDate FROM ClimbingTopSales WHERE      (((ClimbingTopSales.ProductIntroductionDate) = #"      & strQueryDate & "#))"						
STATUS
This problem has been resolved in Visual InterDev 6.0.
MORE INFORMATION
If a date, such as 4/23/96 isentered in the criteria column, then it automatically converts the date tothe following format and successfully runs the query:
   = { ts '1996-04-23 00:00:00' }				
The CommandText parameter reads as follows:
   cmdTemp.CommandText = "SELECT ClimbingTopSales.* FROM ClimbingTopSales   WHERE (ProductIntroductionDate = { ts '1996-04-23 00:00:00' })"				
The above syntax will run in ASP and display the correct results.

Steps to Reproduce Behavior

  1. Create a Web project in Visual InterDev and add a data connection to the AdvWorks database (or a database with a table with a DateTime field).
  2. Add an ASP page and insert a Design Time DataCommand Control.
  3. Edit the Design Time control and go into the SQL Builder. Drop the ClimbingTopSales View onto the Show Diagram Pane in the Query Designer.
  4. Select all columns and the ProductIntroductionDate column with the DataTime type. Clear the output box for the ProductIntroductionDate field.
  5. Enter the following line in the criteria column for the ProductIntroductionDate field:
          = [qryDate]						
  6. Run the query and enter 4/23/96 into the Parameter Value column of the Define Query Parameters dialog box and following error should appear:
    ODBC Error: 22005: [Microsoft][ODBC Microsoft Access 97 Driver] Data type mismatch in criteria expression.
  7. Enter 4/23/96 into the criteria column and run the query. The date is converted to = { ts '1996-04-23 00:00:00' } and the query will now run without displaying the Define Query Parameters dialog box, as this is no longer a parameterized query.
Properties

Article ID: 175258 - Last Review: 12/05/2015 08:07:26 - Revision: 1.5

Microsoft Visual InterDev 1.0 Standard Edition

  • kbnosurvey kbarchive kbpubtypekc kbdtqdesigner kberrmsg kbprb KB175258
Feedback