IP Sorting

[neo651]

I have many tables of network devices in our company and I would like to be able to sort these tables by the IP address.

I'm trying to figure this out but because of the formatting of an IP address there is no way to sort them in Access naturally.



The suggestion I always hear is to break out your IP address into 4 different fields, one for each octet. The problem I have with this is that I can't set "Unique" to the IP field anymore cause it's broken up.

The easy solution is to create 4 split up fields and one combined field but now I have to enter the data twice.

So my question is, is there anyway to do the easy solution without having to enter my data twice -OR- is there a different method of sorting by IP altogether?

Thank you.

[alansidman]

Do each of the four segments of an IP address always contain the same number of characters? If this is the case, you could parse the IP address easily in a query and then do a sort on the query and have your information very quickly.

Alan

[neo651]

That's an interesting question. Each segment of an IP address contains UP TO three digits but as few as one. Technically speaking adding leading zeroes doesn't change the value but by convention you only display the significant digits.

But if it works I'll take it. I'm fairly new to Access, though. Would you mind explaining what you mean when you say I can parse the IP address in a query?

Thank you.

[NTC]

well the sort is a text sort, not a number sort.....and it should sort fine....at least fine in terms of a text sort. identical to sorting social security numbers....

so when you say: no way to sort them in Access naturally... don't really understand as to what is meant.

[alansidman]

I have tried to recreate your db. I put in some dummy IP addresses as text. Look at query 1 and then at query 2 to see how I have parsed them if that is what you are looking for. I did create a query 3 after seeing NTC post. That query sorts the original table. No real difference between parsing the IP addresses and not parsing them.

If you are thinking that the IPs will sort like numbers, lowest to highest, because they are text, they sort all numbers beginning with 1 then 2, then 3 etc. but it will go 12, 245, 32,33,345, 4, 45, 6 etc because they are text.

Alan