Problem regarding ' and "

[Guest]

I knew this is a very 'popular' question but I could not find the proper
answer to solve it.

I have a line of code

sqlstr = "INSERT INTO

VALUES (Date,'" + Comments.value + "');"
docmd.runsql sqlstr

The 'Comments' is a text box that waiting for user's input. The user's input
may include ' and " (for example: It's a "car")

When passing the example to the query, error occurred. I hope in my table,
it will store the date and the complete input (e.g. It's a "car"). Anyone who
know the solution please kindly reply this post as soon as possible because
it is urgent. Thank you.

Cheer,
chonming

 

[Daveo]

Hi there,

Do you mean concatenating the date and comments together? If so, I
reckon you need replace the +'s with &'s:

sqlstr = "INSERT INTO

VALUES (Date,'" & Comments.value & "');"
docmd.runsql sqlstr

HTH - David

 

[Guest]

Thanks Daveo for your quick reply.

But I think you misunderstand my question. I would like to store the comment
that user type in a text box( textbox name: Comments) into table. In that
table, i will also store the date (in another field) the comment was stored.
I used the queries below to perform the append action

sqlstr = "INSERT INTO

VALUES (Date,'" & Comments.value & "');"
docmd.runsql sqlstr

In the comment that user key in, it might contain single quote ( ' ) and
double quote ( " ). Single quote and double quote will cause error while
running the sql. For example, the input is "it's a car". So now

sqlstr = INSERT INTO

VALUES (7/28/2005,'it's a car');

'it's a car' will be an invalid query for SQL. I would like to know the way
to solve this problem without change the string into "its a car".

cheer,
chonming

 

[Guest]

Try this -

sqlstr = "INSERT INTO tblMyTable ( Date, Comments )" & _
"SELECT #" & Format(Date.Value,"mm/dd/yy") & "#, '" & Comments.Value & "'"
docmd.runsql sqlstr

Cheers ...

Thanks Daveo for your quick reply.

But I think you misunderstand my question. I would like to store the comment
that user type in a text box( textbox name: Comments) into table. In that
table, i will also store the date (in another field) the comment was stored.
I used the queries below to perform the append action

sqlstr = "INSERT INTO

VALUES (Date,'" & Comments.value & "');"
docmd.runsql sqlstr

In the comment that user key in, it might contain single quote ( ' ) and
double quote ( " ). Single quote and double quote will cause error while
running the sql. For example, the input is "it's a car". So now

sqlstr = INSERT INTO

VALUES (7/28/2005,'it's a car');

'it's a car' will be an invalid query for SQL. I would like to know the way
to solve this problem without change the string into "its a car".

cheer,
chonming

Hi there,

Do you mean concatenating the date and comments together? If so, I
reckon you need replace the +'s with &'s:

sqlstr = "INSERT INTO

VALUES (Date,'" & Comments.value & "');"
docmd.runsql sqlstr

HTH - David

 

[Guest]

Ooops ... you'll need a space before 'SELECT' ...

Try this -

sqlstr = "INSERT INTO tblMyTable ( Date, Comments )" & _
"SELECT #" & Format(Date.Value,"mm/dd/yy") & "#, '" & Comments.Value & "'"
docmd.runsql sqlstr

Cheers ...

Thanks Daveo for your quick reply.

But I think you misunderstand my question. I would like to store the comment
that user type in a text box( textbox name: Comments) into table. In that
table, i will also store the date (in another field) the comment was stored.
I used the queries below to perform the append action

sqlstr = "INSERT INTO

VALUES (Date,'" & Comments.value & "');"
docmd.runsql sqlstr

In the comment that user key in, it might contain single quote ( ' ) and
double quote ( " ). Single quote and double quote will cause error while
running the sql. For example, the input is "it's a car". So now

sqlstr = INSERT INTO

VALUES (7/28/2005,'it's a car');

'it's a car' will be an invalid query for SQL. I would like to know the way
to solve this problem without change the string into "its a car".

cheer,
chonming

Hi there,

Do you mean concatenating the date and comments together? If so, I
reckon you need replace the +'s with &'s:

sqlstr = "INSERT INTO

VALUES (Date,'" & Comments.value & "');"
docmd.runsql sqlstr

HTH - David

 

[Noozer]

I knew this is a very 'popular' question but I could not find the proper
answer to solve it.

I have a line of code

sqlstr = "INSERT INTO

VALUES (Date,'" + Comments.value + "');"
docmd.runsql sqlstr

The 'Comments' is a text box that waiting for user's input. The user's input
may include ' and " (for example: It's a "car")

That's a real problem.. Your query ends up looking like:

INSERT INTO MyTable VALUES (#2005/07/29#,'It's a "car"');"

....so the second value is only 'It' and the rest of the line causes an
error.

You need to use the REPLACE function to change ' and " to other characters
(like ` and ``).

I'm not aware of other characters to watch for.

 

[Guest]

This WORKS --

Public Sub Y()

Dim s As String
Dim p As Integer

s = "now is """ & "the" & """ time to test it"

MsgBox s

For p = 1 To Len(s)
If Mid(s, p, 1) = Chr(34) Then
Mid(s, p, 1) = Chr(39)
End If
Next

MsgBox s

DoCmd.RunSQL "INSERT INTO tblMyNumbers VALUES ( 103, """ & s & """ )"

End Sub

 

[Noozer]

For p = 1 To Len(s)

If Mid(s, p, 1) = Chr(34) Then
Mid(s, p, 1) = Chr(39)
End If
Next

Long way to do this... Take a look a the REPLACE function.

 

[Marshall Barton]

I knew this is a very 'popular' question but I could not find the proper
answer to solve it.

I have a line of code

sqlstr = "INSERT INTO

VALUES (Date,'" + Comments.value + "');"
docmd.runsql sqlstr

The 'Comments' is a text box that waiting for user's input. The user's input
may include ' and " (for example: It's a "car")

That's a real problem.. Your query ends up looking like:

INSERT INTO MyTable VALUES (#2005/07/29#,'It's a "car"');"

...so the second value is only 'It' and the rest of the line causes an
error.

You need to use the REPLACE function to change ' and " to other characters
(like ` and ``).

Instead of replacing the quotes with some other character,
you should just double up the one that's used to enclose the
string:
.. . . ' & Replace(Comments, "'", "''") & ' . . .
or
.. . . " & Replace(Comments, """", """""") & " . . .
or maybe more readable
.. . . " & Replace(Comments, Chr(34), Chr(34) & Chr(34)) & "
.. . .