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

Runtime error 3075 Syntax error

P: 13
Hi Folks,

I keep getting the error when I select a name with an apostraphe, such as in the name "O'Connor" :

Run-time error '3075':
Syntax error (missing operator) in query expression
'([Surname-Change]='O'Connor')'.


A Debug highlights the line:
Me.LoansReservationsSubform.Form.RecordSource = myCustomerSN

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo18_AfterUpdate()
  2. Dim myCustomerSN As String
  3.  
  4. myCustomerSN = "select * from LoansReservations  where ([Surname-Change] ='" & Me.Combo18 & "')"
  5. Me.LoansReservationsSubform.Form.RecordSource = myCustomerSN
  6. Me.LoansReservationsSubform.Form.Requery
  7. Me.Combo8 = Null
  8. Me.Combo6 = Null
  9. Me.Combo14 = Null
  10. Me.Combo12 = Null
  11. Me.Combo33 = Null
  12. End Sub
  13.  
Any help resolving this would be gratefully received.

Thanks
Feb 18 '19 #1
Share this Question
Share on Google+
3 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,284
The “problem” is simple, because when you have a surname with an apostrophe, the SQL engine can’t differentiate that character from an apostrophe used for identifying strings.

The “solution” is to change all your surnames with an apostrophe to either 1) have no apostrophe (thus OConnor) or 2) change all the “dumb” apostrophes to “smart apostrophes” (thus O’Connor).

Other than that, I know of no other easy workaround. I’ve encountered this error myself when dealing with names.
Feb 18 '19 #2

P: 13
Thanks for your reply twinnyfo. I would probably go with your 'smart apostrophe' suggestion, but I'm not sure what you mean by a "smart apostrophe". In your illustration, it looks like a standard apostrophe, which appears to show no difference from how I input the data and by how the error response looks in my opening sentence. Does this mean that in all databases, an apostrophe can never be used or is this just a quirk in Access?
Feb 19 '19 #3

twinnyfo
Expert Mod 2.5K+
P: 3,284
DEM,

If you look very closely at the apostrophes and quotes in my last post and your post, you will see that mine are slightly curved and yours are straight. (This is because I have been responding on an iPad, which automatically adds smart quotes).

I will put these in a different font to see better:

Expand|Select|Wrap|Line Numbers
  1. My Quotes:  “” ‘’
  2. Your Quotes: "" ''
The issue is not that you can’t “use” straight apostrophes and quotes in Access, the issue is that you can use them in strings in queries—particularly in criteria expressions.

Hope this hepps!
Feb 19 '19 #4

Post your reply

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