How To Fix Access “Type Mismatch In Criteria Expression" Errors

Want to know what fixes are there to solve type mismatch in criteria expression"? Well for the solution you need to read the complete post.  So just stuck till the end….! Start with the practical scenario that is encountered by one of the users like you only.

Practical scenario:

Hi,
I Have been using the same database for years without any trouble. Suddenly we are getting the Data Type Mismatch in Criteria Expression when we attempt to run a VB function.
As I have stated this code worked fine for years and now suddenly we are getting this error.
Thanks in advance for any assistance.

source:

https://www.daniweb.com/programming/databases/threads/257645/access-suddenly-has-data-type-mismatch-in-criteria-expression

Problem description:

Access “Type Mismatch in Criteria Expression” error itself indicates that Access can’t match an input value to the data type it expects for the value.  Here in this post we are going to discuss about the two different type of Type Mismatch error along with their easy fixes.

MS Access “Data Type Mismatch In Criteria Expression" Error

Issue

This particular type mismatch expression error usually comes when you try to use the “insert record” or “update record” server behavior to set the value of the column in Microsoft with an empty string (“ ”).

Reason

Ms Access is well known for its strong data typing, it imposes a set of rules on given column values. The empty string value is the command text (SQL) can’t be saved in Access "Date/Time" data type specified by the particular column.

Solution

Don’t put inserting or updating column of “Date/Time" data types in Access with empty strings, (“ ”) or with any further value that corresponds to the range of values assigned for the data type.

Also Read: MS Access Corrupt Database Recovery Tool to Remove Serious Error Blot

Access Run Time Error '13': Type Mismatch Error

Symptoms

If you dimension an object as a Recordset and then set that object to databaseobject.OpenRecordset(source),  then at that moment you will receive the following error message.

Cause Run time error '13': Type mismatch

If your project contains reference of both the Data Access Objects (DAO) library and the active data objects (ado) library, you may watch multiple Recordset entries in the list when you dimension the recordset object. This error mainly occurs when you list the ADO library with some higher priority than the DAO library in the Reference dialog box. Without even knowing which Recordset entry you select from the list.

Resolution Access Run time error '13': Type mismatch

If you only need the DAO Recordset object, make sure that reference for the DAO object library has higher priority in the Reference dialog box.

If you use both the Recordset objects DAO and ADO, dimension the objects explicitly as follows:

   Dim adoRS As ADODB.Recordset
   Dim daoRS As DAO.Recordset

Steps to reproduce the behaviour

1. Start visual studio. This will open the New Project dialog box.

2. Tap to the Open option and this will open the Form1.

3. Right-click the form1 form and then click view code. The Project1 - Form1 (Code) window appears.

4. Paste the following code in the Project1 code page:

Dim dbs AS Database
Dim rs AS Recordset  ' When you press the spacebar after "AS," the
                           ' list box includes multiple Recordset entries.
                           ' Pick any Recordset item, or type "Recordset."
Private Sub Form_Load()
Set dbs=OpenDatabase(dbname)
Set rs=dbs.OpenRecordset(source)  ' The error occurs when this line
                                        ' of code executes.
End Sub

5. Now on the Project menu, click References.  As this will open the References - Project1 dialog box. 

6. Tap Microsoft DAO 3.x Object Library, after then Microsoft ActiveX Data Objects 2.x Library, and then at the end tap to the OK option.

Also Read: Fixes To Resolve MS Access Error “ID Is Not An Index In This Table”

Note: ADO library must have the higher priority in the References dialog box list above the DAO library.

7. From the Run menu, click to the Start option just to run the program. You will get the error that is mentioned above in the symptom section.

Automatic solution:

Access Repair N Recovery Tool as it is the best tool to recover and repair Access Database. This advance solution provide perfect recovery and restoring of access database with all queries, table’s structure, primary key’s, table’s data and objects. This is the best repair tool to repairs database all corruption issue. It is a perfect solution to get back your corrupted MDB/ACCDB file and all objects.

Conclusion:

Now you must have got a clear idea that what makes this Access Type Mismatch In Criteria Expression and Run Time error 13: Type mismatch error.  And must try the fixes mentioned for both this type mismatch error. If you really find it helpful then don’t forget to send your review regarding this.