How to sort by IP Address

[kbrooks]

I am completely stumped on this one. I have a database where we track IP Addresses and I just want to sort by IP...should be simple, I'd think.

With these 5 IP's, I'd want them in this order:
1.2.3.1
1.2.3.4
1.2.3.7
1.2.3.9
1.2.3.10
1.2.3.17
1.2.3.21

But it sorts in this order:
1.2.3.1
1.2.3.10
1.2.3.17
1.2.3.21
1.2.3.4
1.2.3.7
1.2.3.9

Is there anything I can do to fix this? I want it to see each number as a whole, for example "10" instead of "1" and "0"

Much thanks.

 

[WayneRyan]

kbrooks,

IP numbers aren't really numbers. Sorting them is like sorting
paragraph numbers (as you've seen).

Hopefully you could store them as a set of four components. Since
they can have hex numbers in them, they're still not numbers ...
10 would still be before 9.

You could write a little VBA function to "pad them out" to the
maximum number:

xxxx.xxxx.xxxx.xxxx

Then 9 would come first.

0001.0002.0003.0009
0001.0002.0003.0010

I'm not a network person. Need more specifics about the format
of the IP numbers, but the function should be easy enough to write.
Especially using the Split() function.

Wayne

 

[RV]

Create a query, using the Replace and CLng function.
In SQL view, your query would have to look like:

SELECT tblIPAdres.IPAdres
FROM tblIPAdres
ORDER BY CLng(Replace(tblIPAdres.IPAdres,".",""))

To be amended as per your table and column name.

RV

 

[WayneRyan]

kb,

I voted for the four numeric fields, but there are hex numbers (digits?)
in them. They should still be separate though. I still don't see how
that helps sort them though.

I'd pad it out to "xxxx.xxxx.xxxx.xxxx" and not even guess where the
hex thingies sort out. At least without any changes 9 will be less
than 10 again.

Just add to a query:

SortIP: ExpandIP([WeirdIPThatDoesNotSort])

and put this in a Public Module:

Public Function ExpandIP(IpAddress As String) As String
Dim varCells() As String
varCells = Split(IpAddress, ".")

ExpandIP = String(4 - Len(varCells(0)), "0") & varCells(0) & "." & _
           String(4 - Len(varCells(1)), "0") & varCells(1) & "." & _
           String(4 - Len(varCells(2)), "0") & varCells(2) & "." & _
           String(4 - Len(varCells(3)), "0") & varCells(3)
End Function

btw,

Just kidding, but where do the "hex thingies" sort?

Wayne

 

[kbrooks]

A friend found a Microsoft article addressing this very problem. Very slick!! Thought I'd pass it along in case anyone else has this problem.

http://office.microsoft.com/en-us/assistance/HP011646131033.aspx

Thanks to all of you who offered suggestions!