Syntax error caused by apostrophies in a string

[Robin]

Hi

I have a problem caused by apostrophes. The following builds a string which
is then used to set the control source property of a text box. This works
fine until one of the included name fields contains an apostrophe. I see
why this doesn't work, but I can't see how to fix it!
________________________________________________________

strCtlSrc = "='Some text here " & [FirstName] & " " & [LastName] & "
some text here.'"

Me!MyTextBox.ControlSource = strCtlSrc

________________________________________________________

Any ideas very welcome.

Regards

Robin

 

[Robin]

I have a problem caused by apostrophes. The following builds a string

which is then used to set the control source property of a text box. This
works fine until one of the included name fields contains an apostrophe.
I see why this doesn't work, but I can't see how to fix it!
________________________________________________________

strCtlSrc = "='Some text here " & [FirstName] & " " & [LastName] & "
some text here.'"

Me!MyTextBox.ControlSource = strCtlSrc

________________________________________________________

Ok - cured the problem by using a label caption rather than a text box
control source (probably should have done that in the first place). This
does away with the need to have double quotes around the string.

But I'm still curious if anyone has any suggestions?? Apostrophes in double
quoted strings must come up elsewhere?

Robin

 

[Ken Snell \(MVP\)]

Use the Replace function to double up the ' characters so that ACCESS sees
them correctly:

strCtlSrc = "='Some text here " & Replace([FirstName], "'", "''") & " "
& Replace([LastName], "'", "''") & " some text here.'"

 

[Robin]

Many thanks Ken. I solved the problem a couple of minutes ago, by using a
label.caption instead (didn't need double quotes), but I'll remember this
for the next time.

Regards

Robin

 

[John Spencer]

Another method is to use double quotation marks inside the string you are
building. This is always confusing to me, but I think you could use.

strCtlSrc = "= "" Some text here "" & [FirstName] & "" "" & [LastName] &
"" some text here."""

I have a problem caused by apostrophes. The following builds a string
which is then used to set the control source property of a text box.
This works fine until one of the included name fields contains an
apostrophe. I see why this doesn't work, but I can't see how to fix it!
________________________________________________________

strCtlSrc = "='Some text here " & [FirstName] & " " & [LastName] & "
some text here.'"

Me!MyTextBox.ControlSource = strCtlSrc

________________________________________________________

Ok - cured the problem by using a label caption rather than a text box
control source (probably should have done that in the first place). This
does away with the need to have double quotes around the string.

But I'm still curious if anyone has any suggestions?? Apostrophes in
double quoted strings must come up elsewhere?

Robin

 

[Robin]

Thanks John - I haven't tried it yet but that seems logical.

I agree about the confusion - needs a fair amount of mental acrobatics to
get your head round some of this syntax.