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?