apostraphy in name causing an error 1

[Ollie71]

I have the following in a form that runs from a button to append the information contained in unbound controls on the form.

MySQlLabRegHair = "INSERT INTO LabRegister ([CustomerId], [Lab], [Sample Date], [AHSCode], [Name]) " _
& "VALUES (" & Me![CustomerId] & ", '" & Me![Lab] & "', '" & Me![SampleDate] & "', " & Me![AHSCode] & ", '" & Me![CustomerName] & "') "

Which appends the selected information in the controls to the the table like its supposed too, EXCEPT in one situation.

When the name selected in the control [customerName] contains an apostraphy eg "O'Brien, John", I get the following error
Run time error 3075
Syntax Error (missing operator) in query expression "O'Brien'.

Any other name with hyphens or other characters are fine but every name with an apostraphy ' in it causes the code to fail.
The name field in the table is a string and the [customerName] control is a hidden one that gets its value from a drop down control which concatenates the name from firstname and lastname from the customer details table, which are all strings.

anyone have any ideas why this fails? cant figure out a reason why, when I go to debug after the error the Me![customerName] controls value is "O'Brien, John" just like it should be.

 

[dhookom]

Try:

MySQlLabRegHair = "INSERT INTO LabRegister ([CustomerId], [Lab], [Sample Date], [AHSCode], [Name]) " _
   & "VALUES (" & Me![CustomerId] & ", """ & Me![Lab] & """, """ & Me![SampleDate] & """, " & Me![AHSCode] & ", """ & Me![CustomerName] & """) "

Duane
Hook'D on Access
MS Access MVP

 

[Ollie71]

Nice that works, I tried to do that myself but stuffed it by only having 2 sets of double quote marks, didnt realise it was three.

Wonder why the shorthand version reads the appostrahy as part of the code instead of just a character contained with the string?

I would have thought you could have any character between the double quote marks of a string.

Thanks again

 

[dhookom]

If you used

Debug.Print MySQlLabRegHair

you would see why the single quotes can cause an issue. There are other methods using Chr() or replacing imbedded single quotes with 2 single quotes.

Duane
Hook'D on Access
MS Access MVP