Error when using (') in sql SELECT string (1 Viewer)

[jayke]

Hi,

I have created a search form where people can search for company names. It works fine but I try to search for a company like l'amour then it goes wrong. I get following error

Run-time error '3075':
Syntax error (missing operator) in query expression 'vstNaam LIKE '*l'amour*';'.

I see that it expects a closing ' but how can I get around it? It is part of the search argument.

Here is the code I use: ZoekArgument = search argument = l'amour

   strSQL = "SELECT tblVestigingen.vstVestigingsID, tblVestigingen.vstNaam, tblVestigingen.vstLocStraat, " & _
             "tblVestigingen.vstLocHuisnr, tblVestigingen.vstLocPostcodeID " & _
             "INTO tblZoeken " & _
             "FROM tblVestigingen WHERE " & ZoekOpdracht & " LIKE '*" & ZoekArgument & "*';"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

I hope someone can help me!

grtz jayke

 

[namliam]

Try using this:

   strSQL = "SELECT tblVestigingen.vstVestigingsID, tblVestigingen.vstNaam, tblVestigingen.vstLocStraat, " & _
             "tblVestigingen.vstLocHuisnr, tblVestigingen.vstLocPostcodeID " & _
             "INTO tblZoeken " & _
             "FROM tblVestigingen WHERE [" & ZoekOpdracht & "] LIKE ""*" & ZoekArgument & "*"";"

The catch is however that you now cannot use " in the searchstring.

It is allways smart to use [] around the fieldnames. If you need a realy perminant fix you should replace all ' or " in the "ZoekArgument" with a double ' or " ('' or "") this eliminates the problem all together, but using " instead mosttimes resolves the problem

Regards

=> Met vriendelijke groeten uit Amsterdam/NL