question

BikeGirl123456-4338 avatar image
0 Votes"
BikeGirl123456-4338 asked Viorel-1 rolled back

MsAccess Vba SQL Quotes (single / double)

I have a sql that works fine. In the where-clause I' am using a recordset as parameter.

Dim dbs As DAO.Database

Dim rsData As DAO.Recordset

Dim strsql As String

Set dbs = CurrentDb

Set rsData = dbs.OpenRecordset("Qry_GetEntPersoon")
sqltext = "SELECT KISS_tblENTITEITEN.EntiteitVatting FROM KISS_tblENTITEITEN WHERE ((KISS_tblENTITEITEN.EntiteitVatting)= '" & rsData!KISSEntiteitVatting & "');"

Set qrydeftemp = dbs.CreateQueryDef("xxtempxx", sqltext)

I have a problem when there are quotes in the name, f.e. M'HAMED, then I get a syntax error.
I don't know how to change the single quotes to double quotes in the sql to correct the syntax error.

118163-image.png

It should be "M'HAMED" for the query to run without a syntax error.


office-vba-dev
image.png (20.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 rolled back

For such situations it is recommended to use Parameterised Queries using Parameters property of qrydeftemp. Some examples can be found in documentation and articles.

You can also try an intermediate fix:

sqltext = "SELECT . . . '" & Replace(rsData!KISSEntiteitVatting, "'", "''") & "');"

But you should consider the parameters instead of such concatenation.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.