IP Address Sorting

[G Anderson]

I have a need to track assigned IP addresses and I've chosen MS Access as my
tool. One of the main reasons for tracking, is so I can issue the next
available address in a given segment.

I have tried a few different variations for the fields, but the sorting is
always an issue. A field which contains the whole address (10.106.17.32)
doesn't sort in IP address order with other IP address records. I have
tried 4 fields, one for each octet of the IP and that doesn't sort by IP
address either. If I use a single field and pad the IP address with zeros it
will sort by IP address, but I don't like it.

I'm confident that MS Access is working as designed, but I'm wondering if I
can program around it, change field type, etc. to get it to work the way I
want it to work.

Any suggestions would be appreciated.

Thanks,
G Anderson

 

[Brendan Reynolds]

When you tried the four fields, what was the data type? It should work if
you used a numeric data type, but not if you used text.

 

[Jerry Whittle]

I'll get you started:

SELECT IP_Addresses.IP_Address,
CInt(Left([IP_Address],InStr([IP_Address],".")-1)) AS

,
CInt(Mid([IP_Address],InStr(1,[IP_Address],".")+1,InStr(1,Mid([IP_Address],InStr(1,[IP_Address],".")+1),".")-1)) AS MiddlePart
FROM IP_Addresses
ORDER BY CInt(Left([IP_Address],InStr([IP_Address],".")-1)),
CInt(Mid([IP_Address],InStr(1,[IP_Address],".")+1,InStr(1,Mid([IP_Address],InStr(1,[IP_Address],".")+1),".")-1));

The above will pull out the first two parts of the IP address each in there
own column and turn convert them from text to integers. Then you use the
order by clause to sort them.

I'm thinking that some smart coder, certainly not me, could do it easier in
a module. Time for a Google search on Sort IP Addresses.

Dang! MS has already done it in a similar fashion that I was trying.
http://office.microsoft.com/en-us/assistance/HP011646131033.aspx​

 

[BruceM]

Do you need to sort by all four octets? It is often the case that only the
rightmost octet changes. I ask only because it could simplify the
expression or code that you use.

 

[Bruce D. Meyer]

For what it's worth, what Microsoft wrote here:
Dang! MS has already done it in a similar fashion that I was trying.
http://office.microsoft.com/en-us/assistance/HP011646131033.aspx


actually doesn't work in the real world. The problem is, IP's (at least in
my case) are the unique field (primary key,) and already sorted as an index
which apprantly can't be removed if it is a primary key. I created an
autonumber filed, and made it the primary key, still has same problem. Only
the 'partIV' portion of the sort method microsoft describes will allow you to
choose ascending sort. The other octets fails with an 'invalid procedure call
error.'

--
Bruce D. Meyer, CCNA, MCSE
Network Analyst
City of Columbia, SC

I'll get you started:

SELECT IP_Addresses.IP_Address,
CInt(Left([IP_Address],InStr([IP_Address],".")-1)) AS

,
CInt(Mid([IP_Address],InStr(1,[IP_Address],".")+1,InStr(1,Mid([IP_Address],InStr(1,[IP_Address],".")+1),".")-1)) AS MiddlePart
FROM IP_Addresses
ORDER BY CInt(Left([IP_Address],InStr([IP_Address],".")-1)),
CInt(Mid([IP_Address],InStr(1,[IP_Address],".")+1,InStr(1,Mid([IP_Address],InStr(1,[IP_Address],".")+1),".")-1));

The above will pull out the first two parts of the IP address each in there
own column and turn convert them from text to integers. Then you use the
order by clause to sort them.

I'm thinking that some smart coder, certainly not me, could do it easier in
a module. Time for a Google search on Sort IP Addresses.

Dang! MS has already done it in a similar fashion that I was trying.
http://office.microsoft.com/en-us/assistance/HP011646131033.aspx
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a need to track assigned IP addresses and I've chosen MS Access as my
tool. One of the main reasons for tracking, is so I can issue the next
available address in a given segment.

I have tried a few different variations for the fields, but the sorting is
always an issue. A field which contains the whole address (10.106.17.32)
doesn't sort in IP address order with other IP address records. I have
tried 4 fields, one for each octet of the IP and that doesn't sort by IP
address either. If I use a single field and pad the IP address with zeros it
will sort by IP address, but I don't like it.

I'm confident that MS Access is working as designed, but I'm wondering if I
can program around it, change field type, etc. to get it to work the way I
want it to work.

Any suggestions would be appreciated.

Thanks,
G Anderson

​

 

[Stefan Hoffmann]

hi Bruce,

For what it's worth, what Microsoft wrote here:
Dang! MS has already done it in a similar fashion that I was trying.
http://office.microsoft.com/en-us/assistance/HP011646131033.aspx

Forget this articel, it is completly nonsens.

actually doesn't work in the real world. The problem is, IP's (at least in
my case) are the unique field (primary key,) and already sorted as an index
which apprantly can't be removed if it is a primary key.

When you really store an IP address in your database, you don't have any
problem, as an IP address is a 4 byte number, Long Integer. So no need
to sort it.

http://en.wikipedia.org/wiki/IP_address#IP_version_4


mfG
--> stefan <--

 

[Bruce D. Meyer]

I stand corrected. Shortly after posting the reply above, I did some more
work, and found I had a dirty record which broke the sort. I cleaned the
record, and it now works accurately, and fast.

I have to say, the wikipedia link referred to, is a description of ip, but
not related to the topic her, of sorting IP's. (Unless I missed it while
learning about the European law of IP info remaining private or you risk
losing your ability to run a business and go to jail...

 

[Stefan Hoffmann]

hi Bruce,

I have to say, the wikipedia link referred to, is a description of ip, but
not related to the topic her, of sorting IP's.

An IP is a Long Integer value: "IPv4 only uses 32-bit (4 byte) addresses
[..]"
So when storing an IP as Long Integer, why and how would you sort it?
Its dotted representation is in Msb...Lsb order, so its natural order is
what you like to get.

I assume you do not store it as Long Integer, but as Text. This is a
common mistake as you don't store the real value, but one of its many
representations.

When using a function to create a sort order you should use something like

Public Function DottedIpToLong(ADottedIp As String) As Long
End Function

(Unless I missed it while learning about the European law of IP info remaining private or you risk losing your ability to run a business and go to jail...

Huh? The link i gave you was a link to en.wikipedia.org and had nothing
to do with intellectual property...

mfG
--> stefan <--