Solved

Error Message = Data type mismatch in criteria expression in query

Posted on 2014-09-18
7
590 Views
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?
0
Comment
Question by:Fritz Paul
  • 3
  • 3
7 Comments
 
LVL 1

Accepted Solution

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

Try..

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

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Have you tried printing the SQL string

Debug.print rst2SQL

Which one of these:

111025149084
1980/101391/07

 is the type that is causing the problem?
0
 

Author Comment

by:Fritz Paul
Comment Utility
Hi dale it was this one 111025149084.
Thanks for your contribution.
Above solution from Dodsworth solved it.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Closing Comment

by:Fritz Paul
Comment Utility
Thanks that worked.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
@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.
0
 

Author Comment

by:Fritz Paul
Comment Utility
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

743 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

16 Experts available now in Live!

Get 1:1 Help Now