apostrophe in form

  • Thread starter Thread starter pkeegs
  • Start date Start date
P

pkeegs

I have been trying to make sense of some of the previous answers in the forum
without success, so here is my problem.
My database has a client form from which I can select another form for
additional information if required. When I click on the button to select the
additional information for a person with an apostrophe in the name I get the
following message.

Syntax error (missing operator) in query expression '[Full Name]='John
O'Neill''.

The system generated code is

stLinkCriteria = "[Full Name]=" & "'" & Me![ParameterFullName] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

What do I have to do to get the code working with names containing an
apostrophe?

Cheers
 
You need to replace each of those single quotes in your code
with two double quotes. So if we expand that line a bit so we
can clearly see the single quotes it would look like;

stLinkCriteria = "[Full Name]=" & " ' " & Me![ParameterFullName] & " ' "

then we replace each of those single quotes with, again, two
double quotes and we have;

stLinkCriteria = "[Full Name]=" & " "" " & Me![ParameterFullName] & " "" "

which, in your actual code will look like;

stLinkCriteria = "[Full Name]=" & """" & Me![ParameterFullName] & """"
 
I managed to work out the replace code from Douglas J Steele's posting
12/3/2005 so my code is now

stLinkCriteria = "[Full Name]=" & "'" & Replace(Me![ParameterFullName],
"'", "''") & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

and appears to be working well
 
Thanks Sean, much simpler than the replace option I came up with.

Beetle said:
You need to replace each of those single quotes in your code
with two double quotes. So if we expand that line a bit so we
can clearly see the single quotes it would look like;

stLinkCriteria = "[Full Name]=" & " ' " & Me![ParameterFullName] & " ' "

then we replace each of those single quotes with, again, two
double quotes and we have;

stLinkCriteria = "[Full Name]=" & " "" " & Me![ParameterFullName] & " "" "

which, in your actual code will look like;

stLinkCriteria = "[Full Name]=" & """" & Me![ParameterFullName] & """"

--
_________

Sean Bailey


pkeegs said:
I have been trying to make sense of some of the previous answers in the forum
without success, so here is my problem.
My database has a client form from which I can select another form for
additional information if required. When I click on the button to select the
additional information for a person with an apostrophe in the name I get the
following message.

Syntax error (missing operator) in query expression '[Full Name]='John
O'Neill''.

The system generated code is

stLinkCriteria = "[Full Name]=" & "'" & Me![ParameterFullName] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

What do I have to do to get the code working with names containing an
apostrophe?

Cheers
Click to expand...
Click to expand...
 
Back
Top