' causes an error

F

ftec

The ' in the word Fisher's occasionally causes an sql error?

UPDATE Master SET [Sub Title] = 'Fisher's Arm' WHERE ID = 32;


thanks

Johannes
 
F

ftec

Are you creating this programmatically?

Yes, the " " won't help; the exact error msg will be:

Syntax error (missing operator) in query expression ''"Fisher's Arm"' WHERE
ID = 32;'.


Roger Carlson said:
Use quote marks:
UPDATE Master SET [Sub Title] = "Fisher's Arm" WHERE ID = 32

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


ftec said:
The ' in the word Fisher's occasionally causes an sql error?

UPDATE Master SET [Sub Title] = 'Fisher's Arm' WHERE ID = 32;


thanks

Johannes
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Reread Roger's response. He doesn't have single quotes around Fisher's Arm:
just double ones.

If for some reason you must use single quotes as your delimiter, then your
other option is to change the string by replacing each occurrance of the
delimiter inside the string with two occurrances of the delimiter. Assuming
you're using Access 2000 or higher, this can be accomplished as:

Dim strLookup As String
Dim strSQL As String

strLookup = "Fisher's Arm"
strSQL = "UPDATE Master SET [Sub Title] = '" & _
Replace(strLookup, "'", "''") & "'"

Exagerated for effect, that's

strSQL = "UPDATE Master SET [Sub Title] = ' " & _
Replace(strLookup, " ' ", " ' ' ") & " ' "

You can also use Replace(strLookup, Chr$(39), Chr$(39) & Chr$(39)), or

strSQL = "UPDATE Master SET [Sub Title] = " & Chr$(39) & _
Replace(strLookup, Chr$(39), Chr$(39) & Chr$(39)) & Chr$(39)


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


ftec said:
Are you creating this programmatically?
Click to expand...

Yes, the " " won't help; the exact error msg will be:

Syntax error (missing operator) in query expression ''"Fisher's Arm"' WHERE
ID = 32;'.


Roger Carlson said:
Use quote marks:
UPDATE Master SET [Sub Title] = "Fisher's Arm" WHERE ID = 32

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


ftec said:
The ' in the word Fisher's occasionally causes an sql error?

UPDATE Master SET [Sub Title] = 'Fisher's Arm' WHERE ID = 32;


thanks

Johannes
Click to expand...
Click to expand...
Click to expand...
 
R

Roger Carlson

Another way is to double up the quote marks in the string:
strLookup = "Fisher's Arm"
intID = 32
strSQL = "UPDATE Master SET [Sub Title] = """ & strLookUp & """" & WHERE ID
= " & intID


--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Douglas J. Steele said:
Reread Roger's response. He doesn't have single quotes around Fisher's Arm:
just double ones.

If for some reason you must use single quotes as your delimiter, then your
other option is to change the string by replacing each occurrance of the
delimiter inside the string with two occurrances of the delimiter. Assuming
you're using Access 2000 or higher, this can be accomplished as:

Dim strLookup As String
Dim strSQL As String

strLookup = "Fisher's Arm"
strSQL = "UPDATE Master SET [Sub Title] = '" & _
Replace(strLookup, "'", "''") & "'"

Exagerated for effect, that's

strSQL = "UPDATE Master SET [Sub Title] = ' " & _
Replace(strLookup, " ' ", " ' ' ") & " ' "

You can also use Replace(strLookup, Chr$(39), Chr$(39) & Chr$(39)), or

strSQL = "UPDATE Master SET [Sub Title] = " & Chr$(39) & _
Replace(strLookup, Chr$(39), Chr$(39) & Chr$(39)) & Chr$(39)


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


ftec said:
Are you creating this programmatically?
Click to expand...

Yes, the " " won't help; the exact error msg will be:

Syntax error (missing operator) in query expression ''"Fisher's Arm"' WHERE
ID = 32;'.


Roger Carlson said:
Use quote marks:
UPDATE Master SET [Sub Title] = "Fisher's Arm" WHERE ID = 32

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


"ftec" <ftecatkolumbusfi> wrote in message
The ' in the word Fisher's occasionally causes an sql error?

UPDATE Master SET [Sub Title] = 'Fisher's Arm' WHERE ID = 32;


thanks

Johannes
Click to expand...
Click to expand...
Click to expand...
 
F

ftec

Reread Roger's response. He doesn't have single quotes around Fisher's
Arm:
just double ones.
Click to expand...

This is what I understood i.e. double ones. The error msg i posted
shows how the were interpereted.

I'll try the replacing, thanks.

Douglas J. Steele said:
If for some reason you must use single quotes as your delimiter, then your
other option is to change the string by replacing each occurrance of the
delimiter inside the string with two occurrances of the delimiter. Assuming
you're using Access 2000 or higher, this can be accomplished as:

Dim strLookup As String
Dim strSQL As String

strLookup = "Fisher's Arm"
strSQL = "UPDATE Master SET [Sub Title] = '" & _
Replace(strLookup, "'", "''") & "'"

Exagerated for effect, that's

strSQL = "UPDATE Master SET [Sub Title] = ' " & _
Replace(strLookup, " ' ", " ' ' ") & " ' "

You can also use Replace(strLookup, Chr$(39), Chr$(39) & Chr$(39)), or

strSQL = "UPDATE Master SET [Sub Title] = " & Chr$(39) & _
Replace(strLookup, Chr$(39), Chr$(39) & Chr$(39)) & Chr$(39)


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


ftec said:
Yes, the " " won't help; the exact error msg will be:

Syntax error (missing operator) in query expression '' " Fisher's Arm "
Click to expand...
'
WHERE
ID = 32;'.


Roger Carlson said:
Use quote marks:
UPDATE Master SET [Sub Title] = "Fisher's Arm" WHERE ID = 32

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


"ftec" <ftecatkolumbusfi> wrote in message
The ' in the word Fisher's occasionally causes an sql error?

UPDATE Master SET [Sub Title] = 'Fisher's Arm' WHERE ID = 32;


thanks

Johannes
Click to expand...
Click to expand...
Click to expand...
 
F

ftec

Ok. I got it, this works, I just didn't get right first.

Thanks much
Johannes


Roger Carlson said:
Use quote marks:
UPDATE Master SET [Sub Title] = "Fisher's Arm" WHERE ID = 32

How are you using this? Are you creating this programmatically?
--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


ftec said:
The ' in the word Fisher's occasionally causes an sql error?

UPDATE Master SET [Sub Title] = 'Fisher's Arm' WHERE ID = 32;


thanks

Johannes
Click to expand...
Click to expand...
 
Top