General Question: Single Quotes in Text Fields

[Guest]

From time to time I run into problems while building dynamic "imbedded SQL"
strings, because some of the text I'm grabbing from a table or form has an
apostrophe (AKA single quote) in it. The SQL gets all confused by the extra
quotation mark without a closing partner. Anybody know a workaround for this
other than breaking the user's fingers if he uses apostrophes? It's be great
to have some kind of a function that one could wrap around every text string
to morph apostrophes into some other character or just delete them
altogether. I'll probably try to write one when I get a spare moment, but if
someone has already done so, hey, plagiarism's a virtue when you're writing
code!

 

[Guest]

Try

Replace([FieldName],"'","")

 

[Douglas J. Steele]

Your approach eliminates the apostrophes, Ofer. That's not necessary: you
can simply double them up:

Replace([FieldName],"'","''")

Exagerated for clarity, that's

Replace([FieldName]," ' " ," ' ' " )

Check my May, 2004 "Access Answers" column in Pinnacle Publication's "Smart
Access". You can download the column (and sample database) for free from
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Try

Replace([FieldName],"'","")

--
Good Luck
BS"D

From time to time I run into problems while building dynamic "imbedded
SQL"
strings, because some of the text I'm grabbing from a table or form has
an
apostrophe (AKA single quote) in it. The SQL gets all confused by the
extra
quotation mark without a closing partner. Anybody know a workaround for
this
other than breaking the user's fingers if he uses apostrophes? It's be
great
to have some kind of a function that one could wrap around every text
string
to morph apostrophes into some other character or just delete them
altogether. I'll probably try to write one when I get a spare moment,
but if
someone has already done so, hey, plagiarism's a virtue when you're
writing
code!

 

[Guest]

Hi Larry

1. Use the Replace function if you want to get rid of the '.

or

2. Surround the field with double "'s . i.e. exagerated - " " e.g.

DoCmd.RunSQL "DELETE * FROM table WHERE field= """ & me.field & """"

This will cope with the odd ' without resporting to violence !

Cheers.

BW

 

[Guest]

Thanks Doug,
You right, now I remember that I'm using this method to pass parameters to a
StoredPrcedure in the SQL server, when single quote involved.

--
Good Luck
BS"D

Your approach eliminates the apostrophes, Ofer. That's not necessary: you
can simply double them up:

Replace([FieldName],"'","''")

Exagerated for clarity, that's

Replace([FieldName]," ' " ," ' ' " )

Check my May, 2004 "Access Answers" column in Pinnacle Publication's "Smart
Access". You can download the column (and sample database) for free from
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Try

Replace([FieldName],"'","")

--
Good Luck
BS"D

From time to time I run into problems while building dynamic "imbedded
SQL"
strings, because some of the text I'm grabbing from a table or form has
an
apostrophe (AKA single quote) in it. The SQL gets all confused by the
extra
quotation mark without a closing partner. Anybody know a workaround for
this
other than breaking the user's fingers if he uses apostrophes? It's be
great
to have some kind of a function that one could wrap around every text
string
to morph apostrophes into some other character or just delete them
altogether. I'll probably try to write one when I get a spare moment,
but if
someone has already done so, hey, plagiarism's a virtue when you're
writing
code!

 

[Doc]

Hey guys, I'm having trouble with this too. I tried what you suggested,
here's my string:

"SELECT * FROM tblPayees WHERE Payee = """ & Frm!IDPayeeInput & """"

And I am getting the error:

"Invalid Column Name '(Field Value Shows Here)'"

What have I done wrong?