Data type mismatch Acess 2010 VBA

Question

I get a Data type mismatch error when I try to run the following code:

Dim myString As String
Dim rs As Recordset

sSQL = "SELECT * FROM KLIST WHERE KLIST = " & Me.bleh
Set rs = CurrentDb.OpenRecordset(sSQL)
myString = rs.Fields("DATEK")

The "bleh" field is a normal text field in a form. The field "KLIST" in the "KLIST" table is also a text field, containing numbers.

Does the fact that i enter numbers in the text field in the form change the format of the field? And if it does, how can I compare them in the WHERE statement.

Thanks.

try this one sSQL = "SELECT * FROM KLIST WHERE KLIST = '" & Me.bleh & "'" — Dmitry Pavliv
The format error disappeared, but now it says that there is no such record. I double checked and there is such record. Is it possible that I will get the ASCII code of the number, but not the number itself as a string(text)? — DanielLazarov
try to run this SQL statement in query designer: SELECT * FROM KLIST WHERE TRIM(KLIST) = 'x' where x is value of Me.bleh. Does it retrives your records? — Dmitry Pavliv
and also try this one in VBA: sSQL = "SELECT * FROM KLIST WHERE TRIM(KLIST) = '" & TRIM(Me.bleh) & "'" — Dmitry Pavliv

Olivier Jacot-Descombes Olivier Jacot-Descombes · Accepted Answer · 2014-03-29T17:51:26

If KLIST is a text column, you must add string delimiters (single quotes in SQL):

SELECT * FROM KLIST WHERE KLIST = '123'

In VBA

sSQL = "SELECT * FROM KLIST WHERE KLIST = '" & Me.bleh & "'"

A single quote within a string in SQL can be escaped by doubling the quote:

SELECT * FROM KLIST WHERE KLIST = 'that''s it'

Use the Replace function in VBA to achieve this:

sSQL = "SELECT * FROM KLIST WHERE KLIST = '" & Replace(Me.bleh, "'", "''") & "'"

Data type mismatch Acess 2010 VBA

2 Answers