By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,029 Members | 1,336 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,029 IT Pros & Developers. It's quick & easy.

How do I stop an apostrophe causing a syntax error?

P: 39
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:
Expand|Select|Wrap|Line Numbers
  1. varvalue = Me.Combo29.Column(1) 
  2.  
  3. row = "SELECT  [name]  FROM organisations WHERE [organisations].[governing body] = '" & varvalue & "'" 
  4.  
  5. Name1.RowSource = row 
  6.  
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
Aug 10 '10 #1

✓ answered by jimatqsi

You might try changing your code to this:

Expand|Select|Wrap|Line Numbers
  1. 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

Share this Question
Share on Google+
5 Replies


Expert 100+
P: 1,240
You might try changing your code to this:

Expand|Select|Wrap|Line Numbers
  1. 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
Aug 11 '10 #2

P: 39
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
Aug 11 '10 #3

100+
P: 675
You don't have to give up either quote, single(') or double("), for this. Substitute Replace(varvalue, """", """""") for varvalue in
Expand|Select|Wrap|Line Numbers
  1. row = "SELECT  [name]  FROM organisations WHERE [organisations].[governing body] = """ & varvalue & """
giving you
Expand|Select|Wrap|Line Numbers
  1. row = "SELECT  [name]  FROM organisations WHERE [organisations].[governing body] = """ & Replace(varvalue, """", """""") & """
This way your organisation can continue to use either quote, as appropriate.
Aug 11 '10 #4

NeoPa
Expert Mod 15k+
P: 31,769
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).
Aug 11 '10 #5

100+
P: 675
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:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. row = "SELECT  [name]  " & _
  2.       "FROM organisations " & _
  3.       "WHERE [organisations].[governing body] = '" & _
  4.       Replace(varvalue, "'", "''") & "'"
Aug 12 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.