Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Equivelant code/command (Access 97)

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

    James

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Equivelant code/command (Access 97)

    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.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Equivelant code/command (Access 97)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Equivelant code/command (Access 97)

    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.
    <pre>Function ReplaceAll(varIn As Variant, _
    varFind As Variant, _
    varNew As Variant) As Variant
    <font color=448800> 'Created by Charlotte Foust
    'Replaces all instances of varFind in the passed varIn </font color=448800>
    Dim intFindLen As Integer
    Dim intFindPos As Integer
    Dim varOutput As Variant

    <font color=448800> 'initialize the variables</font color=448800>
    varOutput = varIn
    intFindLen = Len(varFind & "")
    intFindPos = 0

    If Not IsNull(varIn) Or IsNull(varFind) Then
    <font color=448800> 'If varIn contains input or if a varNew
    'will replace the existing contents,
    'See if varFind exists in varIn.</font color=448800>
    If IsNull(varFind) Then
    varOutput = varNew
    Else
    intFindPos = InStr(varIn, varFind)
    If intFindPos > 0 Then
    <font color=448800> 'If varFind exists, replace all instances.</font color=448800>
    Do
    varOutput = Left(varOutput, intFindPos - 1) _
    & varNew _
    & Mid(varOutput, intFindPos + intFindLen)
    intFindPos = InStr(intFindPos + 1, varOutput, varFind)
    Loop Until intFindPos = 0
    End If <font color=448800>'intFindPos > 0 </font color=448800>
    End If <font color=448800>'IsNull(varFind) </font color=448800>
    End If <font color=448800>'Not IsNull(varIn) Or IsNull(varFind) </font color=448800>
    ReplaceAll = varOutput
    End Function<font color=448800> 'ReplaceAll(varIn As Variant, _
    varFind As Variant, _
    varNew As Variant) As Variant </font color=448800> </pre>

    Charlotte

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Equivelant code/command (Access 97)

    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.
    Attached Files Attached Files

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Equivelant code/command (Access 97)

    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

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Equivelant code/command (Access 97)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •