sorting by IP

[nbuwalda]

Hello, im trying to setup a query in MSOFFICE Access 2003 where it will sort my information in Ascending order. Im trying to sort by IP Address and have been able to get the following results:

126.0.0.1
126.0.0.10
126.0.0.100
126.0.0.101
126.0.0.103
126.0.0.104
126.0.0.148
126.0.0.179
126.0.0.2
126.0.0.201
126.0.0.203
126.0.0.228
126.0.0.287
126.0.0.5

and so on.....I have been trying to figure out how to get this to work by setting up Criterias but have not been successfull. I was wondering if anyone knew how to get it to sort correctly.

 

[dcx693]

The whole problem stems from the ip addresses being treated as text. When Access (or most programs) sort text, 126.0.0.287 comes before 126.0.0.5 for the same reason that when dealing with months as text, December comes before July.

To correctly sort the data, you'll need to break up the ip addresses into four pieces and sort on those 4 fields. You can then piece them back together for display purposes. It's a bit tricky, but use Val and the string manipulation functions Left, Instr, and Mid to split the data.

For example, the get the first part, use: Val([ip_address]). If you give it Val("126.0.0.287"), that will give you 126.

To get the 2nd part, use Instr to find the location of the 1st and 2nd periods. Subtract the location of the 2nd from the 1st to get the length of the 2nd part. Use the Mid function add 1 to the location of the first period. Use Mid("string",location of 1st period+1,length of 2nd part) to get the 2nd part.

 

[nbuwalda]

Ok, I now understand how Access sorts the data but still dont understand how to make it sort by your instructions. Am I suppose to input these in the Criteria fields? or somewhere else? When I put the Val([126.0.0.81]) in a criteria field it tells me ' " is not a valid name. Make sure it does not include invalid characters or punctuation and that it is not to long.'

 

[nbuwalda]

The IP addresses are already entered into a database and I want to query them from the database so then I have a report showing them in order.

 

[DALeffler]

You almost need a custom function to sort the IP address strings the way you want to (or the way I think you want to).

In the query design grid with the field list showing the table that has your IP addresses:

Field1: myIPAddressesFieldName
table: myTable
Show: true
Sorted: not sorted

Field2: Expr1:fncIPSort([myIPAddresseFieldName])
Show: false
Sorted: ascending

In a standard code module in your database, create the following code:

Function fncIPSort(strIPAdr As String) As String
Dim I As Integer
For I = 1 To 3
fncIPSort = fncIPSort & Format(Mid(strIPAdr, 1, InStr(1, strIPAdr, ".")), "000")
strIPAdr = Mid(strIPAdr, InStr(1, strIPAdr, ".") + 1)
Next I
fncIPSort = fncIPSort & Format(strIPAdr, "000")
End Function

Then run the query.

Hth,

Doug.

 

[dcx693]

Ok, I now understand how Access sorts the data but still dont understand how to make it sort by your instructions. Am I suppose to input these in the Criteria fields? or somewhere else? When I put the Val([126.0.0.81]) in a criteria field it tells me ' " is not a valid name. Make sure it does not include invalid characters or punctuation and that it is not to long.'

Let's say the ip address is contained in a table called tblAddresses inside a field called ip_address.

Create a blank query, add the tblAddresses table to it. Now add the ip_address field to the query grid. In the next column over (which is blank), enter this expression in the "Field:" line, right next to where ip_address is: Val([ip_address]). No need for any criteria. Run the query. You should get the first part of your ip address.

Try at least to get that far. If you need help creating queries, it's a basic thing. Go to the Access on-line help.

 

[dcx693]

Here's another idea for a custom function:

Function FormatIPAddress(strIPOriginal As String) As String
Dim strTemp() As String
Dim intX As Integer

    '- Use the split function to take apart the original
    '-   ip address string and put it into the strTemp array
    strTemp() = Split(strIPOriginal, ".")
    
    '- Format each element of the array so that it has 3 digits
    '-  and put the formatted string back together
    For intX = 0 To UBound(strTemp)
        FormatIPAddress = FormatIPAddress & "." & _
            Format(strTemp(intX), "000")
    Next intX
    
    '- Chop off the initial .
    FormatIPAddress = Mid(FormatIPAddress, 2)
    
End Function

 

[Jon K]

Since each IP starts with 126.0.0., you can simply sort the rest of the digits i.e.

Field: CInt(Mid([IP],9))
Sort: Ascending
Show: uncheck