Combo box search

[jax]

I am using a combo box on a form for users to search for the item they want to see , ie choose the name of an office from a list and the form detailing all the information is shown. This is working great apart from it wont search for names that have apostrophes in them. I get the "Syntax error" message. Does anyone know how I get around this, I need the names to be correctly spelled for the Reports.

 

[Alexandre]

I suspect that you build a WHERE clause to search the fields. Quotes need to be surrounded (''') to be interpreted correctly in SQL statements.
You may find the following functions helpfull:

Public Function PrepareStringForSQL(ByVal sValue As String) As String
Dim sAns As String

    sAns = Replace(sValue, Chr(39), "''")
    sAns = "'" & sAns & "'"
    PrepareStringForSQL= sAns

    sAns = ""
End Function


Public Function Replace(ByVal varValue As Variant, ByVal strFind As String, _
                        ByVal strReplace As String) As Variant

    
On Error GoTo Ooops:

    
    Dim intLenFind As Integer
    Dim intLenReplace As Integer
    Dim intPos As Integer

          
    If IsNull(varValue) Then
        Replace = Null
    Else
        intLenFind = Len(strFind)
        intLenReplace = Len(strReplace)

              
        intPos = 1
        Do
            intPos = InStr(intPos, varValue, strFind, 0)
            If intPos > 0 Then
                varValue = left(varValue, intPos - 1) & strReplace & _
                            Mid(varValue, intPos + intLenFind)
                intPos = intPos + intLenReplace
            End If
        Loop Until intPos = 0
    End If
    Replace = varValue


ExitReplace:
Exit Function


Ooops:
    DoCmd.Beep
    DoCmd.Echo True
    DoCmd.Hourglass False
    MsgBox "Um erro aconteceu na função Replace: " & Error$
    Resume ExitReplace
End Function

PrepareStringForSQL("O'Brien") will return 'O'''Brien' that you can use directly in you WHERE clause:

"WHERE [Name]= 'O'''Brien'"

Alex

[This message has been edited by Alexandre (edited 03-12-2002).]