Apostrophe in field

[geekay]

I am facing a problem. One of my SQL statemnts is some what like that given below

"SELECT TableName.* FROM TableName WHERE TableName.FieldName='" & rs!SchoolName & "'"

When the SchoolName contains an apostrophe, error is resulted. Some SchoolNames can not avoid apostrophe character (Like St. Mary's Secondary School)

I am sorting out the problem by replacing all the Apostrophe charaters ( ' ) by the Grave Accent character ( ` ) in the database manually. I can write a seperate public procedure for this replacement.

I don't find it as a better solution as my database is updated by the user from a delimited text file which is mailed by the end users.

Any other solution to suggest?

Thank you for reading this posting and hope to get your responses

 

[RuralGuy]

Just use double quotes:
"SELECT TableName.* FROM TableName WHERE TableName.FieldName= " & Chr(34) & rs!SchoolName & Chr(34) & ""
or
"SELECT TableName.* FROM TableName WHERE TableName.FieldName=""" & rs!SchoolName & """"

 

[geekay]

Thank you RuralGuy
The second will not work as I am assigning the SQL statement to a string variable in a sub procedure. But first I think is OK. Thanks again for the idea

 

[RuralGuy]

You're welcome. Glad to help.

 

[XLEAccessGuru]

Try this

Hey there,

Try this:

Function ExcludeApQu(MyStr As String) As String
'ExcludeApQu removes apostrophes (') and quotes (") from a string

    ExcludeApQu = Replace(MyStr, Chr(34), "")
    ExcludeApQu = Replace(ExcludeApQu, Chr(39), "")
 
End Function

I use it in a form my customer use to submit help tickets. It works great. Put this in as a public function then on the after update event of every field you want to check for quotes or apostrophes, call this function like so:

Me.FieldName = ExcludeApQu(Me.Field1)

Good luck!!!