Runtime error 3075 Syntax error

[DeltaEchoMikeIT]

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.LoansReserva tionsSubform.Fo rm.RecordSource = myCustomerSN

Private Sub Combo18_AfterUpdate()
Dim myCustomerSN As String

myCustomerSN = "select * from LoansReservations  where ([Surname-Change] ='" & Me.Combo18 & "')"
Me.LoansReservationsSubform.Form.RecordSource = myCustomerSN
Me.LoansReservationsSubform.Form.Requery
Me.Combo8 = Null
Me.Combo6 = Null
Me.Combo14 = Null
Me.Combo12 = Null
Me.Combo33 = Null
End Sub

Any help resolving this would be gratefully received.

Thanks

[twinnyfo]

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.

[DeltaEchoMikeIT]

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?

[twinnyfo]

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:

My Quotes:  “” ‘’
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—particu larly in criteria expressions.

Hope this hepps!