Datatype mismatch errors in Access parameterized Date query (175258)


The information in this article applies to:

  • Microsoft Visual InterDev 1.0
This article was previously published under Q175258

SYMPTOMS

When using the Query Designer to execute a parameterized query based upon a DateTime field against an Access data source, you will receive the following 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 this behavior:
  • 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 is entered in the criteria column, then it automatically converts the date to the 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.

REFERENCES

For the latest Knowledge Base articles and other support information on Visual InterDev and Active Server Pages, see the following page on the Microsoft Technical Support site:

http://support.microsoft.com/ph/3022

Modification Type:MinorLast Reviewed:3/9/2005
Keywords:kbPubTypeKC kbMSCCSearch kbDtQDesigner kberrmsg kbprb KB175258
©2004 Microsoft Corporation. All rights reserved.