Equivelant code/command (Access 97)

Topic

New Reply

Does anyone know of a VB/VBA equivalent statement(s) of COBOL’s INSPECT REPLACING statement?

James

Reply | Quote

Replies

Try explaining what the COBOL function does for those of us who never succumbed to COBOL. Access 97/VBA 5 did not have a built-in replace function (Access 2000/VBA 6 does have one), but most of us built our own routines to accomplish the same thing. Tell us what you’re trying to do and someone will be able to provide you with some code assistance.

Reply | Quote

COBOL’s INSPECT REPLACING looks through the given string for a given character or series of characters and replaces them with whatever you specified.

Basically, I want to find all single quotes in a string and make it 2 single quotes.

Anyone know an easy method which also does it extra faast with minimual processor and resource overhead?

James

Reply | Quote

Here’s a routine I wrote in Access 97 to do what you want. You could make it more efficient by replacing the variants with string variables since those take less memory than variants. I think I used variants in the first place because it allowed me to search for things like chr(10) and other non-printing characters in a string.

Function ReplaceAll(varIn As Variant,  _
                    varFind As Variant, _
                    varNew As Variant) As Variant
  'Created by Charlotte Foust
  'Replaces all instances of varFind in the passed varIn  
  Dim intFindLen As Integer
  Dim intFindPos As Integer
  Dim varOutput As Variant
    
   'initialize the variables 
  varOutput = varIn
  intFindLen = Len(varFind & "")
  intFindPos = 0
  
  If Not IsNull(varIn) Or IsNull(varFind) Then
     'If varIn contains input or if a varNew
    'will replace the existing contents,
    'See if varFind exists in varIn. 
    If IsNull(varFind) Then
      varOutput = varNew
    Else
      intFindPos = InStr(varIn, varFind)
      If intFindPos > 0 Then
         'If varFind exists, replace all instances. 
        Do
          varOutput = Left(varOutput, intFindPos - 1) _
                    & varNew _
                    & Mid(varOutput, intFindPos + intFindLen)
          intFindPos = InStr(intFindPos + 1, varOutput, varFind)
        Loop Until intFindPos = 0
      End If 'intFindPos > 0   
    End If 'IsNull(varFind)   
  End If 'Not IsNull(varIn) Or IsNull(varFind)   
  ReplaceAll = varOutput
End Function 'ReplaceAll(varIn As Variant,  _
                    varFind As Variant, _
                    varNew As Variant) As Variant   

Reply | Quote

Thanks a million – its done the job!

Pretty efficient code if you ask me. Thanks for the advice about changing the variants to strings for resource conservation as well!

James

Reply | Quote

Office2000 has a convenient Replace() function, but I see you still are using 97. If you have Internet Explorer 5.x then you can use the RegExp object in the scripting library. See the attached module for a pre-written replace function.

Reply | Quote

I had a look at it – it seems to be very efficient and what I am looking for. Problem is that I cannot assume they have Access 2000 or IE5.x.

And being a missions organisation in Asia, we have VBScripting turned off on the email side to help cut down on viruses.

Thanks for the suggestion anyway.

James

Reply | Quote