I put all strings through a function, Str4DB before I put them in the SQL
string. Exmaple and definition below:

Set rs = db.OpenRecordset("SELECT * FROM tbldata WHERE Tech LIKE '" &
Str4DB(Left$(cboTechSearch, 25)) & "*'", dbOpenDynaset, adLockOptimistic)



Public Function Str4DB(strOriginalString As String) As String
    Dim str As String, iPos As Integer
    If IsNull(strOriginalString) Then
        str = Null2Str(strOriginalString)
    Else
        str = ReplaceAll(strOriginalString, "'", "''")
    End If
    Str4DB = str
End Function



Public Function Null2Str(varVal As Variant, Optional default As String) As
String
    On Error GoTo Err_Null2str

    If IsNull(varVal) Then
        If IsMissing(default) Then
            Null2Str = ""
        Else
            Null2Str = default
        End If
    Else
        Null2Str = Trim$(varVal)
    End If
    Exit Function

Err_Null2str:
    Null2Str = ""
    Exit Function
End Function



'***************************************************************************
'
'*   Procedure   : ReplaceAll
'*   Description : Used to replace all occurances of strFindString with
strReplaceString in
'*                 strMainString.
'*   Parameters  : strMainString. The string that contains the characters
the need replacing.
'*                 strFindString  The string that needs replacing.
'*                 strReplaceString is the replacement string.
'*   Returns     : String.
'***************************************************************************
'
Public Function ReplaceAll(strMainString As String, strFindString As String,
strReplaceString As String) As String

  Dim lngLenMain As Long
  Dim lngStartPos As Long
  Dim lngFoundPos As Long
  Dim strNew As String ' string to build with replacements (initialy blank)

  lngLenMain = Len(strMainString)
  lngStartPos = 1

  Do While lngStartPos <= lngLenMain
    lngFoundPos = InStr(lngStartPos, strMainString, strFindString, 0) ' 0 
case sensitive
    If lngFoundPos = 0 Then ' No (more) instances
      ' Append remainder of strMainString to strNew
      strNew = strNew & Right$(strMainString, lngLenMain - (lngStartPos -
1))
      Exit Do
    Else
      ' Found a (new) instance. Replace it.
      ' ie. add the part of strMainString JUST scanned prior to instance
      ' then replacement string
      strNew = strNew & Mid$(strMainString, lngStartPos, lngFoundPos -
lngStartPos) & strReplaceString
      lngStartPos = lngFoundPos + Len(strFindString)
    End If
  Loop

  ReplaceAll = strNew

End Function


-----Original Message-----
From: rpsmith@b... [mailto:rpsmith@b...]
Sent: 21 November 2002 17:35
To: professional vb
Subject: [pro_vb] query on a database when data contains an apostrophe


Hello,
I am having trouble doing a query on a database when the data contains an
apostrophe (i.e. O'Hogan  )
I read the Microsoft Article #178070 but don't see how to incorporate it
into my code below.
(I am using VB6.0 and Access2000 database).

Here is my query code
(works great until it hits the name with an apostophe):

Set rs = db.OpenRecordset("SELECT * FROM tbldata WHERE Tech LIKE '" & _
    Left(cboTechSearch, 25) & "'" & "& '*'", dbOpenDynaset,
adLockOptimistic)


Does anyone have any idea how to code this query?
Thanks,
Regan
rpsmith@b...