special characters

[PeterM]

I have a memo field in table A that contains " (special character). I need
to update table B with the memo field from table A. However, Access
compalins that the string used to update table B is not formatted properly
because the memo field in table A contains " characters. How do I enclose
the memo field in an update statment to accommodate that?

sqlstring = "UPDATE [table_A] " _
& " SET [comments] = " & Chr(34) & table_B!Comments & Chr(34)
DoCmd.RunSQL sqlstring

thanks in advance for your help!

 

[Douglas J. Steele]

If the comment doesn't include any single quotes, you can use

sqlstring = "UPDATE [table_A] " _
& " SET [comments] = " & Chr(39) & table_B!Comments & Chr(39)
DoCmd.RunSQL sqlstring

If it does (or you're not sure), use

sqlstring = "UPDATE [table_A] " _
& " SET [comments] = " & Chr(34) & Replace(table_B!Comments,
Chr(34), Chr(34) & Chr(34)) & Chr(34)
DoCmd.RunSQL sqlstring

(This assumes that table_B is a recordset: you can't refer to values in
tables that way).

Incidentally, it's more efficient to use a constant than the function:

Const cDblQtes = """"

sqlstring = "UPDATE [table_A] " _
& " SET [comments] = " & cDblQtes _
& Replace(table_B!Comments, cDblQtes, cDblQtes & cDblQtes) _
& cDblQtes
DoCmd.RunSQL sqlstring

(This avoids the repeated calls to the function)

It's also preferable (in my opinion) to use the Execute method, rather than
RunSQL:

Const cDblQtes = """"

sqlstring = "UPDATE [table_A] " _
& " SET [comments] = " & cDblQtes _
& Replace(table_B!Comments, cDblQtes, cDblQtes & cDblQtes) _
& cDblQtes
CurrentDb.Execute sqlstring, dbFailOnError

(Using Execute avoids the "You're about to update ..." message. Using
dbFailOnError means that a trappable error will occur if something's wrong)

 

[Dirk Goldgar]

In

I have a memo field in table A that contains " (special character).
I need to update table B with the memo field from table A. However,
Access compalins that the string used to update table B is not
formatted properly because the memo field in table A contains "
characters. How do I enclose the memo field in an update statment
to accommodate that?

sqlstring = "UPDATE [table_A] " _
& " SET [comments] = " & Chr(34) & table_B!Comments &
Chr(34) DoCmd.RunSQL sqlstring

thanks in advance for your help!

Your SQL is incorrect (and would do the opposite of what you said, even
if it would work), but I'm going to assume that's just a quickie
example, so I won't try to fix it. To solve the issue you asked about,
try this:

Const Q As String = """"
Const QQ As String = """"""

sqlstring = "UPDATE [table_A] SET [comments] = " & _
Chr(34) & Replace(table_B!Comments, Q, QQ) & Chr(34)

 

[Douglas J. Steele]

In

I have a memo field in table A that contains " (special character).
I need to update table B with the memo field from table A. However,
Access compalins that the string used to update table B is not
formatted properly because the memo field in table A contains "
characters. How do I enclose the memo field in an update statment
to accommodate that?

sqlstring = "UPDATE [table_A] " _
& " SET [comments] = " & Chr(34) & table_B!Comments &
Chr(34) DoCmd.RunSQL sqlstring

thanks in advance for your help!

Your SQL is incorrect (and would do the opposite of what you said, even if
it would work), but I'm going to assume that's just a quickie example, so
I won't try to fix it. To solve the issue you asked about, try this:

Const Q As String = """"
Const QQ As String = """"""

sqlstring = "UPDATE [table_A] SET [comments] = " & _
Chr(34) & Replace(table_B!Comments, Q, QQ) & Chr(34)

Any reason why not

Const Q As String = """"
Const QQ As String = """"""

sqlstring = "UPDATE [table_A] SET [comments] = " & _
Q & Replace(table_B!Comments, Q, QQ) & Q

 

[Dirk Goldgar]

In

Any reason why not

Const Q As String = """"
Const QQ As String = """"""

sqlstring = "UPDATE [table_A] SET [comments] = " & _
Q & Replace(table_B!Comments, Q, QQ) & Q

No reason whatever. I've been up all night working, that's all.

 

[Tom Wannabe]

that's just ridiculous man

you should replace double-quotes with a single quote or even just get rid of
them


and btw; aren't you forgetting a where clause on your update statement??

In

Any reason why not

Const Q As String = """"
Const QQ As String = """"""

sqlstring = "UPDATE [table_A] SET [comments] = " & _
Q & Replace(table_B!Comments, Q, QQ) & Q

No reason whatever. I've been up all night working, that's all.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

In

that's just ridiculous man

you should replace double-quotes with a single quote or even just get
rid of them

Meaning what, Aaron? Changing the customer's data? That would go over
really well. As for whether one would prefer using single-quotes or
double-quotes as the internal text delimiter, it seems to me that should
depend on what you think is least likely to occur in the data to be
quoted. I'm betting that in most cases, single-quotes are likely to
occur more often. Therefore, it makes more sense to use double-quotes
as delimiters and only have to replace embedded double-quotes rarely.

and btw; aren't you forgetting a where clause on your update
statement??

Read my original post, if you actually care.