apostrophe in form

[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

 

[Beetle]

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] & """"

 

[pkeegs]

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

 

[pkeegs]

Thanks Sean, much simpler than the replace option I came up with.

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

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