Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 632
  • Last Modified:

Error Message = Data type mismatch in criteria expression in query

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
Fritz Paul
Asked:
Fritz Paul
  • 3
  • 3
1 Solution
 
DodsworthCommented:
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
 
Dale FyeCommented:
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
 
Fritz PaulAuthor Commented:
Hi dale it was this one 111025149084.
Thanks for your contribution.
Above solution from Dodsworth solved it.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
Fritz PaulAuthor Commented:
Thanks that worked.
0
 
Dale FyeCommented:
@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
 
Fritz PaulAuthor Commented:
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
 
Dale FyeCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now