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.
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.


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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

821 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