single quotes in a query

[gm456]

How can I get Access to perform a SQL query on a name that includes a quote mark -

eg:

SELECT *
FROM people
WHERE NAME= >> o'donnell << that!

No matter what combination of single or double quotes I try, it ain't working.

TIA guys! [sig][/sig]

 

[Guest_imported]

Here is the code I use. Paste this into a general module and pass your field to it for modification before you put it into your sql string.

useage could look like this...
dim lastName as string
lastName = SpecialCharChk(Your Last Name Field)
mySql = &quot;Select * From myTable Where myTable.LastName like '&quot; & lastName & &quot;'&quot;
----------------------------------------------------------
Public Function SpecialCharChk(strToChk As Variant) As String
Dim strNew As String
Dim strTemp As String
Dim iPos As Integer
Dim iLen As Integer
Dim bDone As Boolean
Dim bCharFound As Boolean

If Not (IsNull(strToChk)) Then
strTemp = strToChk
iLen = Len(strToChk)

Do Until bDone
iPos = InStr(strTemp, &quot;'&quot
If iPos > 0 Then
bCharFound = True
'double up the special char
strNew = strNew & Left(strTemp, iPos) & &quot;'&quot;
'dont retrive remaining string if ipos = last char
If iLen <> iPos Then
strTemp = Mid(strTemp, iPos + 1)
Else
strTemp = &quot;&quot;
End If
Else
bDone = True
End If
Loop

If bCharFound Then
If Len(strTemp) > 0 Then
strNew = strNew & strTemp
End If
SpecialCharChk = strNew
Else
SpecialCharChk = strToChk
End If
Else
SpecialCharChk = &quot;&quot;
End If

End Function
--------------------------------------------------------- [sig]<p>John A. Gilman<br><a href=mailto:gms@uslink.net>gms@uslink.net</a><br>[/sig]

 

[kctaylordotcodotuk]

&quot;SELECT * FROM people WHERE NAME=&quot; & Chr$(34) & &quot;o'donnell&quot; & Chr$(34) & &quot;;&quot;
[sig]<p>Keith C Taylor<br><a href=mailto:TekTips@kctaylor.co.uk>TekTips@kctaylor.co.uk</a><br><a href=

http://www.kctaylor.co.uk>The

Information Gardener</a><br>The C stands for Computer![/sig]