How do I stop an apostrophe causing a syntax error?

[copleyuk]

Hi everyone,


I am using a simple lookup function to set the row source of a combo box based on the value set in another combo box.

This is working perfectly but I am getting a syntax error:

syntax error (missing operator) in query expression '[organisations].[governing body] = 'N'Land

This error only occurs when the selected value in box one contains an apostrophe!
I assume this is because the apostrophe effects the way VBA sees the string so it changes the search criteria?

This is the code being used:

varvalue = Me.Combo29.Column(1) 
 
row = "SELECT  [name]  FROM organisations WHERE [organisations].[governing body] = '" & varvalue & "'" 
 
Name1.RowSource = row 
 

Obviously the simple answer would be to go through and remove any apostrophes but this is not really the way I would like to resolve this if there is a better way!?

Thanks in advance

Carl
edit reply report

[jimatqsi]

You might try changing your code to this:

row = "SELECT  [name]  FROM organisations WHERE [organisations].[governing body] = """ & varvalue & """

It changes your text delimiter to a double-quote. Of course, if there are any double-quotes in the data, the same thing will happen. There have to be some rules, if you let the users do whatever they want, this sort of thing will happen.

Jim

[copleyuk]

Spot on thanks Jim!

Unfortunately I inherited the database with the 's already in.

I will make sure going forward the "'s are definately not permitted!!!

Thanks for your help you're a star

[OldBirdman]

You don't have to give up either quote, single(') or double("), for this. Substitute Replace(varvalue, """", """""") for varvalue in

row = "SELECT  [name]  FROM organisations WHERE [organisations].[governing body] = """ & varvalue & """

giving you

row = "SELECT  [name]  FROM organisations WHERE [organisations].[governing body] = """ & Replace(varvalue, """", """""") & """

This way your organisation can continue to use either quote, as appropriate.

[NeoPa]

While OB is right about using Replace() (or other bespoke function) to handle quotes of either type in the data, I would point out that as far as standard SQL is concerned, the Single-Quote (') is the only correct one to use (See Quotes (') and Double-Quotes (") - Where and When to use them).

There is an article by Frinavale that covers the use of this function and why it is a good idea to use it as standard (SQL Injection Attack).

[OldBirdman]

The 'name' field of the table contains both single and double quotes. Assume for one record the name is T. J. "Bud" O'Reilly. I saw no reason this couldn't be allowed in the future. copleyuk's statement that:

I will make sure going forward the "'s are definately not permitted!!!

sort of changes reality, and it was to this sentence I was commenting.
Of course NeoPa is correct - he almost always is. So I would propose:

row = "SELECT  [name]  " & _
      "FROM organisations " & _
      "WHERE [organisations].[governing body] = '" & _
      Replace(varvalue, "'", "''") & "'"