Update Method

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having a problem with the Update method. When I am using numbers, it
works fine. However when I am using words, it has syntax errors. It appears
to error out when there are apostrophie's in the words. I am using it in an
open form. Here is the code:

CurrentDb.Execute "UPDATE [Master_tbl] " & _
"SET [Master_tbl].[Second Name] = " & [Forms]![Name_frm]![Second
Name].[Value] & " Wheren ID = " & Me.ID & "; "

If the name has an apostrophie (ie..."Ryan's Place"), it causes a syntax
error. However when there is a number (ie..."615") it replaces the old value
fine. Is there anyone out there that knows how to fix this problem?
 
JJ said:
I am having a problem with the Update method. When I am using
numbers, it works fine. However when I am using words, it has syntax
errors. It appears to error out when there are apostrophie's in the
words. I am using it in an open form. Here is the code:

CurrentDb.Execute "UPDATE [Master_tbl] " & _
"SET [Master_tbl].[Second Name] = " & [Forms]![Name_frm]![Second
Name].[Value] & " Wheren ID = " & Me.ID & "; "

If the name has an apostrophie (ie..."Ryan's Place"), it causes a
syntax error. However when there is a number (ie..."615") it replaces
the old value fine. Is there anyone out there that knows how to fix
this problem?
Click to expand...

Text literals in the SQL string must be enclosed in quotes, either
single-quotes (') or double-quotes ("). This can be especially tricky
if the quoted text itself contains the quote character you're using to
enclose it, because then it looks to the parser like a premature end to
the quoted literal. If that might happen, you can double-up the quotes
inside the text -- two quotes inside a quoted string are interpreted as
just one.

In your specific case, since your name may contain the single-quote (or
apostrophe), you'd do best to use the double-quote to delimit it. For
convenience, I usually use Chr(34) to define the double-quote character.
So long as you don't think your [Second Name] field will contain the
double-quote character, you can use code like this:

CurrentDb.Execute _
"UPDATE [Master_tbl] " & _
"SET [Second Name] = " & _
Chr(34) & [Forms]![Name_frm]![Second Name] & Chr(34) & _
" WHERE ID = " & Me.ID

If you think that field may contain a double-quote (e.g., 'John "Wild
Man" Smith'), you need to take an extra step to double-up the internal
quote. Watch this:

Dim Q As String

Q = Chr(34)

CurrentDb.Execute _
"UPDATE [Master_tbl] " & _
"SET [Second Name] = " & _
Q & _
Replace([Forms]![Name_frm]![Second Name], _
Q, _
Q & Q) & _
Q & _
" WHERE ID = " & Me.ID
 
Back
Top