Alternate to Replace() in access 97

[chris-uk-lad]

Hi all,

I currently have some code that works great in 2000 but i need to use it in 97.

The issue i have is with

Trim(Mid(Replace(strLine, "'", "''"), 12, 27))

The code reads in a name from a string in a text document and inserts it into a table via Docmd.Execute "Insert...". The issue i face is with a name like O'Neil which causes an error with the SQL hence the replace function for the '.

Is there another way tro check for ' and replace with '' ?

Thanks

 

[SimonB1978]

Hi,

Have a look there: http://www.mvps.org/access/strings/str0004.htm

That might be your solution.

Simon B.

 

[RuralGuy]

Or there is always:

Function onespace2(pStr As String, pDelim As String) As String
'*******************************************
'Purpose: Removes excessive spaces from a string
' and replaces remaining spaces with user
' selected delimiter.
'Coded by: raskew 'Inputs: From debug window:
' ? onespace2(" the quick brown fox", ".")
'Output: "the.quick.brown.fox"
'*******************************************
   Dim strHold As String
   strHold = RTrim(pStr)
   Do While InStr(strHold, " ") > 0
      strHold = left(strHold, InStr(strHold, " ") - 1) & Mid(strHold, InStr(strHold, " ") + 1)
   Loop
   strHold = Trim(strHold)
   Do While InStr(strHold, " ") > 0
      strHold = left(strHold, InStr(strHold, " ") - 1) & pDelim & Mid(strHold, InStr(strHold, " ") + 1)
   Loop
   onespace2 = Trim(strHold)
End Function


Function Replace97( _
         Expression As String, _
         Find As String, _
         sReplace97 As String, _
         Optional start As Long = 1, _
         Optional Count As Long = -1, _
         Optional Compare As Long) As String

   Dim pos As Long
   Dim strTmp As String
   Dim LenFind As Long
   Dim LenReplace97 As Long
   Dim cnt As Long

   If Len(Expression) = 0 Then Exit Function
   If start > Len(Expression) Then Exit Function

   If Len(Find) = 0 Then
      Replace97 = Expression
      Exit Function
   End If

   If start < 1 Or start > Len(Expression) Then start = 1
   If Compare < 0 Or Compare > 2 Then Compare = 0

   strTmp = Expression
   LenFind = Len(Find)
   LenReplace97 = Len(sReplace97)
   pos = InStr(start, strTmp, Find, Compare)

   Do While pos > 0 And cnt <> Count
      strTmp = left$(strTmp, pos - 1) & sReplace97 & Mid$(strTmp, pos + LenFind)
      pos = InStr(pos + LenReplace97, strTmp, Find, Compare)
      cnt = cnt + 1
   Loop

   Replace97 = strTmp

End Function