' causes an error

[Roger Carlson]

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]

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

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


thanks

Johannes

 

[ftec]

Oh, and I'm using Access 97 db with vb6.

thanks
Johannes

 

[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;'.

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

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

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


thanks

Johannes

 

[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)

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;'.

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

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

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


thanks

Johannes

 

[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

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)

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;'.

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

 

[ftec]

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

just double ones.

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.

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)

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

Syntax error (missing operator) in query expression '' " Fisher's Arm "

'

WHERE
ID = 32;'.

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

 

[ftec]

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

Thanks much
Johannes

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

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

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


thanks

Johannes