Question

  • Creator
    Topic
  • #2246114

    Is this a bug or the proper operation of InStr in Access?

    Locked

    by matthewboh ·

    I’m trying to parse out the numbers within an IP address in order to turn them into a range that I can test within Access. I’m having a problem with creating the length of the field for the Mid$ statement.

    Here’s the IP address:

    172.16.160.0

    What’s happening is this statement returns 8 which is the correct:

    InStr(InStr(1,[Firewall Rules]![Source IP],”.”)+1,[Firewall Rules]![Source IP],”.”)+1

    If I take that EXACT same InStr statement and put a minus sign in front:

    -InStr(InStr(1,[Firewall Rules]![Source IP],”.”)+1,[Firewall Rules]![Source IP],”.”)+1

    It returns a -6

    Does it start stepping through the string backwards?

    Figured it out – was negating the +1 so just wrapped it all into parentheses and it works like this:

    -(InStr(InStr(1,[Firewall Rules]![Source IP],”.”)+1,[Firewall Rules]![Source IP],”.”)+1)

All Answers

  • Author
    Replies
    • #2879283

      Clarifications

      by matthewboh ·

      In reply to Is this a bug or the proper operation of InStr in Access?

      Clarifications

    • #2879282

      Quick first one…

      by tobif ·

      In reply to Is this a bug or the proper operation of InStr in Access?

      I haven’t analysed it yet, but obviously, the value of InStr(InStr(1,[Firewall Rules]![Source IP],”.”)+1,[Firewall Rules]![Source IP],”.”) equals 7.
      7+1 = 8
      -7+1 = -6
      🙂

    • #2879281

      Now, let’s analyze it

      by tobif ·

      In reply to Is this a bug or the proper operation of InStr in Access?

      Let’s write the expression the following way:

      InStr(InStr(1,”172.16.160.0″,”.”)+1,”172.16.160.0,”.”)+1

      As you can see, I “expanded” you variable ref to the string you were using. Then I marked the inner expression with italics and the outer expression in semi-bold. The “+1″ I left as it was.

      In order to analyze the outer expression, you first need to determine the value of the inner expression [InStr(1,”172.16.160.0″,”.”)] where you start searching for a dot in position 1. You’ll get a value of 4 in this case. Then we add 1, and get “5”, which we feed into the outer expression.

      In the outer expression, we then evaluate: [InStr(5,”172.16.160.0″,”.”)] i.e. we’re searching for the first dot, starting in position 5, and we’ll find the dot after “16”, which is in position 7. So the value of the whole expression, except the final “+1” is 7.

      In other words, this expression gives the position of the second dot in the argument, and then it adds the value of one.

      By the way, if the argument contains only one dot, the value of the outer expression becomes 0. If the argument contains no dot at all, then you’ll also get the value of 0.

      Alternative approach Why don’t you tell us what you need? There may be some easier way.

    • #2879177

      If you want the parts of the IP

      by tony hopkinson ·

      In reply to Is this a bug or the proper operation of InStr in Access?

      use the split function

      Dim strParts() As String

      strParts = Split([Firewall Rules]![Source IP],”.”)
      So in your example
      strParts(2) would be “160”

      Be a damn sight easier to read and therefore understand as well.

Viewing 3 reply threads