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

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:

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

InStr(InStr(1,[Firewall Rules]!,".")+1,[Firewall Rules]!,".")+1

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

-InStr(InStr(1,[Firewall Rules]!,".")+1,[Firewall Rules]!,".")+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]!,".")+1,[Firewall Rules]!,".")+1)

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Quick first one...

by TobiF In reply to Is this a bug or the prop ...

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

Collapse -

Now, let's analyze it

by TobiF In reply to Is this a bug or the prop ...

Let's write the expression the following way:


As you can see, I "expanded" you variable ref to the string you were using. Then I marked the <i>inner</i> expression with italics and the <b>outer</b> 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,"",".")] 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,"",".")] 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.

Collapse -

If you want the parts of the IP

by Tony Hopkinson In reply to Is this a bug or the prop ...

use the split function

Dim strParts() As String

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

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

Related Discussions

Related Forums