Sorting IP Addresses 2

[burritonator]

I have written the following query to produce a list of IP addresses and related info:

SELECT IP, DepartmentName, NetworkID, Manufacturer, Model
FROM tblDevices
WHERE IP<>""
ORDER BY IP;

The problem that I'm having is that the IPs are being sorted in alphabetical order, meaning that, for instance, all of the IPs in the range 192.9.205.150-192.9.205.159 fall between 192.9.205.15 and 192.9.205.16 in the result set. What changes could I make to the query above so that the IPs in the result set will be sorted in numeric order from 192.9.205.1-192.9.205.255?

Thanks

 

[BNPMike]

You need to take the four substrings (demarkated by the full stops) separately, turn them into numbers and then order by the numeric fields you have generated.

 

[traingamer]

PHV had a function for this in these Access fora in the last 6 weeks, but I can't find it.

 

[RoyVidar]

Here's one thread707-894787, and another thread702-1012706, and here's a couple, too thread181-97503 (hint, advanced search, enter Sort IP and search by subject;-))

Roy-Vidar

 

[traingamer]

Advanced Search

I'll have to use that next time.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[PHV]

In a standard code module create the following function:

Public Function ip2num(ip)
Dim i, a, n
If Len(Trim(Nz(ip, ""))) = 0 Then
  ip2num = 0
  Exit Function
End If
a = Split(ip, ".")
n = CDbl(0)
For i = 0 To UBound(a)
  n = n * 256 + a(i)
Next i
ip2num = n
End Function

And now your query:
SELECT IP, DepartmentName, NetworkID, Manufacturer, Model
FROM tblDevices
WHERE IP<>""
ORDER BY ip2num(IP);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[burritonator]

Thanks PHV. That worked like a charm. However, I have an additional question, because I'm apparently overlooking something in the settings of the report that I am generating based on the query. When I view the query results, the IPs are sorted in the correct order. When I view my report, however (which has its Record Source property set to the query), the IPs are still in alphabetical order instead of the correct numeric order.

It seems that the report is re-sorting the IPs into a different order than what is produced by the query. I'm guessing that when I used the Report Wizard to initially create the report, I probably specified that the records should be sorted based on the IP address field, but now I can't find where to change that. In the settings for the report, the "Order By" field is blank and "Order By On" is set to "No".

Can anyone provide any insight into how to make sure the report displays the results in the same order as the query from which it obtains its data?

Thanks

 

[BNPMike]

Reports do re-sort. Click on the Sorting and Grouping Icon in Report Design and enter the new field for sorting

 

[burritonator]

I need some additional advice. In my IP address report, I want for the records to be displayed in the exact same order that they are in when I view the result set for the query that the report pulls its data from. I used PHV's function to sort the query results, so the order is correct. I don't need for the report to re-sort the records at all. That's where the problem lies. Access forces me to enter at least one sort field under "Sorting and Grouping", which causes the order of the records in the report to be different from the order of the query's result set. How can I solve this problem?

Thanks

 

[PHV]

Have you put ip2num(IP) in the SELECT list ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[burritonator]

I have the query coded exactly as you had it in your previous post:

SELECT IP, DepartmentName, NetworkID, Manufacturer, Model
FROM tblDevices
WHERE IP<>""
ORDER BY ip2num(IP);

That is the only place that I have "ip2num(IP)" entered (besides in the function's code). Should it be specified somewhere else? (I wasn't sure what you meant by SELECT list - I didn't know if you were referring to the query or a setting in the report).

Thanks

 

[PHV]

SELECT IP, DepartmentName, NetworkID, Manufacturer, Model, ip2num(IP) AS numIP
FROM tblDevices
WHERE IP<>""
ORDER BY ip2num(IP);

You can now group and sort by numIP.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[burritonator]

That did the trick. I should have known that, but I'm rather rusty. Thanks for all of the help.