PRB: Datatype Mismatch Errors in Access Parameterized Date Query

• Microsoft Visual InterDev, version 1.0

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

Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

If a date, such as "4/23/96" (without the single quotation marks) 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' }

   cmdTemp.CommandText = "SELECT ClimbingTopSales.* FROM ClimbingTopSales
   WHERE (ProductIntroductionDate = { ts '1996-04-23 00:00:00' })"

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/support/vinterdev/

Keywords          : kbDtQDesigner kberrmsg
Technology        : kbInetDev
Version           : WINDOWS:1.0
Platform          : WINDOWS
Issue type        : kbprb