Error Message = Data type mismatch in criteria expression in query

Posted on 2014-09-18
Last Modified: 2014-09-18
I have a table tblReportsToCreate with field ClientID datatype Text.
I Have a query s_qryLinearInfo based on that table.
The ClientID is sometimes a personal identity number and sometimes a company registration number so may look like 111025149084 and sometimes it looks like this 1980/101391/07.

In VBA code I declare another DAO recordset rst1 from this s_qryLinearInfo and loop though the records. During each loop I need to collect data with respect to the active ClientID and creates another DAO recordset as follows:

'(Dims done at the top of the procedure.)
Dim strClientId As String
Dim rst2SQL As String
Dim rst2 As DAO.Recordset

'In each loop through rst1 I state
strClientId = rst1![CLIENTID]

'Then I create the string rst2SQL
rst2SQL = "SELECT s_tblPositionLevelInformation.* FROM s_tblPositionLevelInformation WHERE (s_tblPositionLevelInformation.CLIENTID)=" & strClientId

'(s_tblPositionLevelInformation also has a text field named CLIENTID with the same field values as above.)

Set rst2 = db.OpenRecordset(rst2SQL, dbOpenSnapshot)      'THIS IS WHERE THE PROBLEM ARRISES.

This works fine while the ClientID is a company registration number, but when it is a personal Identity number I get
Error Message = Data type mismatch in criteria expression.

What should I do?
Question by:Fritz Paul
  • 3
  • 3

Accepted Solution

Dodsworth earned 500 total points
ID: 40330917
I'm guessing your problem is that your where clause is not enclosed in quotes.


rst2SQL = "SELECT s_tblPositionLevelInformation.* FROM s_tblPositionLevelInformation WHERE (s_tblPositionLevelInformation.CLIENTID)='" & strClientId & "'"
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40331112
Have you tried printing the SQL string

Debug.print rst2SQL

Which one of these:


 is the type that is causing the problem?

Author Comment

by:Fritz Paul
ID: 40331175
Hi dale it was this one 111025149084.
Thanks for your contribution.
Above solution from Dodsworth solved it.
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.


Author Closing Comment

by:Fritz Paul
ID: 40331179
Thanks that worked.
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40331320
@Fritz Paul,

seems strange that Access would interpret the one that has two "/" embedded within the string correctly (as a string) and return the right record, but would generate an error for the one that is purely numeric.

Which version of Access are you using?  I would like to test this and determine whether there is  a bug that needs to be addressed.

Author Comment

by:Fritz Paul
ID: 40331400
The fields in the tables are defined as text. But the issue came because I did not have enough quotes, so when it saw the CLIENTID's that looked like numbers, it somehow decided it must be numbers and not text, but it did not have issues with the ids with slashes in.

So Dodsworth pointed out that I needed more quotes.

Was :
rst2SQL = "SELECT s_tblPositionLevelInformation.* FROM s_tblPositionLevelInformation WHERE (s_tblPositionLevelInformation.CLIENTID)=" & strClientId

Changed to:
rst2SQL = "SELECT s_tblPositionLevelInformation.* FROM s_tblPositionLevelInformation WHERE (s_tblPositionLevelInformation.CLIENTID)='" & strClientId & "'"

Does that make sense?

Thanks for your interest.

Office version.
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40331410
Yes, the necessity of quotes was clear to me from the start, but since Dodson had already pointed that out, I was trying to ascertain which version of Access (2010 32 bit) you are running so that I can do a similar test.

Access should not have handled that correctly in either instance, so there may be a bug that needs to be addressed.

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now