Microsoft KB Archive/175258

= Datatype mismatch errors in Access parameterized Date query =

Article ID: 175258

Article Last Modified on 4/23/2007

-

APPLIES TO


 * Microsoft Visual InterDev 1.0 Standard Edition

-



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
<ol> 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).</li> Add an ASP page and insert a Design Time DataCommand Control.</li> Edit the Design Time control and go into the SQL Builder. Drop the ClimbingTopSales View onto the Show Diagram Pane in the Query Designer.</li> Select all columns and the ProductIntroductionDate column with the DataTime type. Clear the output box for the ProductIntroductionDate field.</li>  Enter the following line in the criteria column for the ProductIntroductionDate field: <pre class="fixed_text"> = [qryDate] </li> 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.

</li> 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.</li></ol>

<div class="references_section">