How to handle single quotes from text control

[Greg Snidow]

Greetings all. I am using Access 2000 and SQL Server 2000. I have a form
with subform for comments. I was receiving errors that said "the data will
be saved but can not be displayed...", and found a KB article saying it was
an issue with ADP 2000. So, my solution was to change the way people enter
notes. I put an unbound text box, txtAddNote, and a command button,
cmdAddNote, then run some SQL to insert the note, and then requery the
subform. The problem is that I did not think about what would happen if
someone entered in single quotes, such as "Joe's from VA". I can not expect
all users to enter it like Joe''s, nor can I expect them to remember to never
use single quotes. So, my question is, how can I write it so that the users
can type whatever they want? The below is what I have currently working.
*******************************
On Error GoTo Err_cmdAddNote_gotfocus

Dim strSQL As String
strSQL = "INSERT INTO tblcmc_comments
(ewo,entered_dt,entered_by,comments) " & _
"SELECT '" & Me.txtEWO & "',GETDATE(),SYSTEM_USER,'" &
Me.[txtAddNote] & "'"

DoCmd.RunSQL strSQL
Me.frmSearchEWO_CommentsSub.Form.Requery
Me.txtAddNote = ""


Exit_cmdAddNote_gotfocus:
*********************************************
The last field in the INSERT statement, comments, is the field in question.
Thank you.

 

[Greg Snidow]

Well, as usual, I posted without thoroughly researching. Anyhow, I found the
REPLACE function to be useful here.

Instead of '" & Me.txtAddNote & "'

I used '" & REPLACE(Me.txtAddNote," ' "," '' ") & "'

While I can not make this cause an error, I would still like to know if this
is the best way, and if not, what is? Thank you.

 

[Douglas J. Steele]

If you want to keep the apostophes in your text, use

' " & REPLACE(Me.txtAddNote," ' "," ' ' ' ") & " '

Your way actually changes the text. Whether or not that matters depends on
what's in the notes. If, for example, you've got feet and inches or minutes
and seconds, that can be significant.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Well, as usual, I posted without thoroughly researching. Anyhow, I found
the
REPLACE function to be useful here.

Instead of '" & Me.txtAddNote & "'

I used '" & REPLACE(Me.txtAddNote," ' "," '' ") & "'

While I can not make this cause an error, I would still like to know if
this
is the best way, and if not, what is? Thank you.

Greetings all. I am using Access 2000 and SQL Server 2000. I have a
form
with subform for comments. I was receiving errors that said "the data
will
be saved but can not be displayed...", and found a KB article saying it
was
an issue with ADP 2000. So, my solution was to change the way people
enter
notes. I put an unbound text box, txtAddNote, and a command button,
cmdAddNote, then run some SQL to insert the note, and then requery the
subform. The problem is that I did not think about what would happen if
someone entered in single quotes, such as "Joe's from VA". I can not
expect
all users to enter it like Joe''s, nor can I expect them to remember to
never
use single quotes. So, my question is, how can I write it so that the
users
can type whatever they want? The below is what I have currently working.
*******************************
On Error GoTo Err_cmdAddNote_gotfocus

Dim strSQL As String
strSQL = "INSERT INTO tblcmc_comments
(ewo,entered_dt,entered_by,comments) " & _
"SELECT '" & Me.txtEWO & "',GETDATE(),SYSTEM_USER,'" &
Me.[txtAddNote] & "'"

DoCmd.RunSQL strSQL
Me.frmSearchEWO_CommentsSub.Form.Requery
Me.txtAddNote = ""


Exit_cmdAddNote_gotfocus:
*********************************************
The last field in the INSERT statement, comments, is the field in
question.
Thank you.

 

[Greg Snidow]

Thanks for the reply Doug. I do not understand, though. I tested my way,
which was to replace a single apostrophe with double apostrophes, and it
works. I tested it all sorts of ways, including having nothing but
apostrophes in my notes, adding it to the end of numbers as if in feet and
seconds, and everything I tried succesfully saved my text as entered. Your
solution below, with single apostrophes being replaced with triple
apostrophes, causes the same 'closing delimiter not found...' error. Am I
missing something?

Greg

If you want to keep the apostophes in your text, use

' " & REPLACE(Me.txtAddNote," ' "," ' ' ' ") & " '

Your way actually changes the text. Whether or not that matters depends on
what's in the notes. If, for example, you've got feet and inches or minutes
and seconds, that can be significant.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Well, as usual, I posted without thoroughly researching. Anyhow, I found
the
REPLACE function to be useful here.

Instead of '" & Me.txtAddNote & "'

I used '" & REPLACE(Me.txtAddNote," ' "," '' ") & "'

While I can not make this cause an error, I would still like to know if
this
is the best way, and if not, what is? Thank you.

Greetings all. I am using Access 2000 and SQL Server 2000. I have a
form
with subform for comments. I was receiving errors that said "the data
will
be saved but can not be displayed...", and found a KB article saying it
was
an issue with ADP 2000. So, my solution was to change the way people
enter
notes. I put an unbound text box, txtAddNote, and a command button,
cmdAddNote, then run some SQL to insert the note, and then requery the
subform. The problem is that I did not think about what would happen if
someone entered in single quotes, such as "Joe's from VA". I can not
expect
all users to enter it like Joe''s, nor can I expect them to remember to
never
use single quotes. So, my question is, how can I write it so that the
users
can type whatever they want? The below is what I have currently working.
*******************************
On Error GoTo Err_cmdAddNote_gotfocus

Dim strSQL As String
strSQL = "INSERT INTO tblcmc_comments
(ewo,entered_dt,entered_by,comments) " & _
"SELECT '" & Me.txtEWO & "',GETDATE(),SYSTEM_USER,'" &
Me.[txtAddNote] & "'"

DoCmd.RunSQL strSQL
Me.frmSearchEWO_CommentsSub.Form.Requery
Me.txtAddNote = ""


Exit_cmdAddNote_gotfocus:
*********************************************
The last field in the INSERT statement, comments, is the field in
question.
Thank you.

 

[Sylvain Lafontaine]

Changing to an unbound text box is one solution but looking at why you are
receiving this error message could be another solution; otherwise, you can
be hit by this problem again and again while creating other subforms.

The easiest way to see what's happening would be probably to use the
SQL-Server Profiler to see what's going between Access and SQL-Server.

 

[Douglas J. Steele]

Aargh. I'm wasn't saying to replace with triple apostrophes.

The second argument is one apostrophe enclosed in double quotes " ' "

The third argument is two apostrophes enclosed in double quotes " ' ' "

Believe me, it works.

Your way, 10' 6" (ten feet, six inches) becomes 10" 6" (ten inches, six
inches)

My way, it stays as 10' 6" in the database.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for the reply Doug. I do not understand, though. I tested my way,
which was to replace a single apostrophe with double apostrophes, and it
works. I tested it all sorts of ways, including having nothing but
apostrophes in my notes, adding it to the end of numbers as if in feet and
seconds, and everything I tried succesfully saved my text as entered.
Your
solution below, with single apostrophes being replaced with triple
apostrophes, causes the same 'closing delimiter not found...' error. Am I
missing something?

Greg

If you want to keep the apostophes in your text, use

' " & REPLACE(Me.txtAddNote," ' "," ' ' ' ") & " '

Your way actually changes the text. Whether or not that matters depends
on
what's in the notes. If, for example, you've got feet and inches or
minutes
and seconds, that can be significant.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Well, as usual, I posted without thoroughly researching. Anyhow, I
found
the
REPLACE function to be useful here.

Instead of '" & Me.txtAddNote & "'

I used '" & REPLACE(Me.txtAddNote," ' "," '' ") & "'

While I can not make this cause an error, I would still like to know if
this
is the best way, and if not, what is? Thank you.

:

Greetings all. I am using Access 2000 and SQL Server 2000. I have a
form
with subform for comments. I was receiving errors that said "the data
will
be saved but can not be displayed...", and found a KB article saying
it
was
an issue with ADP 2000. So, my solution was to change the way people
enter
notes. I put an unbound text box, txtAddNote, and a command button,
cmdAddNote, then run some SQL to insert the note, and then requery
the
subform. The problem is that I did not think about what would happen
if
someone entered in single quotes, such as "Joe's from VA". I can not
expect
all users to enter it like Joe''s, nor can I expect them to remember
to
never
use single quotes. So, my question is, how can I write it so that the
users
can type whatever they want? The below is what I have currently
working.
*******************************
On Error GoTo Err_cmdAddNote_gotfocus

Dim strSQL As String
strSQL = "INSERT INTO tblcmc_comments
(ewo,entered_dt,entered_by,comments) " & _
"SELECT '" & Me.txtEWO & "',GETDATE(),SYSTEM_USER,'" &
Me.[txtAddNote] & "'"

DoCmd.RunSQL strSQL
Me.frmSearchEWO_CommentsSub.Form.Requery
Me.txtAddNote = ""


Exit_cmdAddNote_gotfocus:
*********************************************
The last field in the INSERT statement, comments, is the field in
question.
Thank you.