Solved

Error Message = Data type mismatch in criteria expression in query

Posted on 2014-09-18
7
612 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 1

Accepted Solution

by:
Dodsworth earned 500 total points
ID: 40330917
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 48

Expert Comment

by:Dale Fye
ID: 40331112
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
ID: 40331175
Hi dale it was this one 111025149084.
Thanks for your contribution.
Above solution from Dodsworth solved it.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Closing Comment

by:Fritz Paul
ID: 40331179
Thanks that worked.
0
 
LVL 48

Expert Comment

by:Dale Fye
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.
0
 

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.
0
 
LVL 48

Expert Comment

by:Dale Fye
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.
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

632 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