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.

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).]