Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Instr() Function (Access 97)

    Hi All,

    I have a variable length string that I pass into a strVariable. Within the string there are two instances of a five-character string.

    Dim StrVariable as String
    Dim intBeginHere as Interger
    Dim intBeginWhere as Interger
    Dim intNewPosition as Interger
    Dim str1stPart As String
    Dim str2ndPart As String

    StrVariable = "abcdefg XXXXX hijklmnop XXXXX qrstuvwxyz" 'Can be between 175 and 200 characters.

    intBeginHere = CInt(InStr(StrVariable, "XXXXX") - 1)

    str1stPart = Left(StrVariable, intBeginHere)

    Debug.Print str1stPart

    'Up to this point everything works fine.

    intBeginWhere = intBeginHere + 6

    'I must be missing something because here is where the problem begins. The below InStr() function is _
    not returning the correct number of characters beginning at intBeginWhere until if finds the next _
    "XXXXX". It seems to be adding all the characters from position one. I've checked the help file _
    and the example it gives indicates I should be able to do this. Any ideas why this is not working?

    intNewPosition = CInt(InStr(intBeginWhere, StrVariable, "XXXXX") - 1)

    Debug.Print intNewPosition

    'I even tried

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Instr() Function (Access 97)

    InStr(Position, String1, String2) always returns a number of characters from the start of String1, not from Position. But that should not be a problem if you want to extract the second part.

    intBeginHere = InStr(strVariable, "XXXXX") - 1
    str1stPart = Left(strVariable, intBeginHere)
    intBeginWhere = intBeginHere + 6
    intNewPosition = InStr(intBeginWhere, strVariable, "XXXXX")
    str2ndPart = Mid(strVariable, intBeginWhere, intNewPosition - intBeginWhere)

    Note: InStr returns a numeric result, so there is no need to use CInt.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Instr() Function (Access 97)

    Thanks!

Posting Permissions

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