Reserve Characters in ADO SQL Statement

[nvwildfire]

Hello list, hope this is easy.

My problem is I have a text field(txtDescription) on a form that may contain reserved characters in SQL, mainly '. When I try to pass the text that contains a ' in txtDescription to an SQL statement in my VBA Code (I'm using ADO) I get a "Syntax error (Missing Operator) in query expression: ....." error message.

My question is should I

a) have a procedure remove all reserve characters from the string before I pass it to a SQL statement.

b) have a procedure add a ' before any reserved character in the string before passing it to an SQL statment.

c) give an error to the user telling them they cannot use certain characters.

d) answer the problem another way, ie someone smarter showing me the correct way to get around this problem.

any help would be greatly appreciated.

thanks,

kgk

 

[Orion45]

In our app. we ran into this problem with the apostrophe(') character. We implemented a simple global function in a module which converts the ' to the escape character of ''. The code is as follows...

Public Function FixString2(sInput As String) As String
Dim TempFix As String
If InStr(1, sInput, &quot;'&quot;, vbTextCompare) <> 0 Then
        FixString2 = Replace(sInput, &quot;'&quot;, &quot;''&quot;, 1)
    Else
        FixString2 = sInput
End If
End Function

Any string can then be passed to this function like Fixstring2(strTextbox)
Hope this helps!

 

[cmmrfrds]

Access has a Replace function that you can use. Example.

var1 = Replace(var, "'", "''", , , vbTextCompare)