Hi callumwatson
Apostrophes cause a problem as they are used to indicate in coding a change from code to a literal value (or from a literal value back to code) e.g.
="Today's date is " & Date()
The first part is a fixed value (within the normally double quotation marks - but single can be used) and the Date() part is a function within Access to retreive today's date from the computer's internal clock.
Simmilarly if you look at coding (behind a form or within a module) any comments are preceeded by a single quotation or apostrophe - with the resulting comment turning green. Hence the problem with a value containing an apostrophe - the workings of Access intrepets this as part text and then a change to/from code.
Below is a function that can be saved as a Public Module which you can then call for each instance when searching for a word that contains an apostrophe.
--------------------------------------------
Public Function Apostrophe(strSFieldString As String) As String
--------------------------------------------------------
On Error GoTo Err_Apostrophe
If InStr(strSFieldString, "'") Then
Dim intILen As Integer
Dim intIi As Integer
Dim intApostr As Integer
intILen = Len(strSFieldString)
intIi = 1
Do While intIi <= intILen
If Mid(strSFieldString, intIi, 1) = "'" Then
intApostr = intIi
strSFieldString = left(strSFieldString, intApostr) & "'" & _
right(strSFieldString, intILen - intApostr)
intILen = Len(strSFieldString)
intIi = intIi + 1
End If
intIi = intIi + 1
Loop
End If
Apostrophe = strSFieldString
Exit_Apostrophe:
Exit Function
Err_Apostrophe:
MsgBox Err.Description & _
" - (Error No:" & Err.Number & ")"
Resume Exit_Apostrophe
End Function
-------------------------------------------
HTH
Rich Gorvin
