Sort IP addresses in Access

Introduction

If you work with IP addresses, you probably already know that it is not as straightforward as working with text or numbers. This is because an IP address is really a collection of four numeric values separated by a period (.), where each value is a number between 0 and 255. The following table shows data before any sort order is applied.

Sorting IP addresses in Access is a challenge because Access does not provide a special data type for storing IP addresses. Although an IP address is just a collection of numbers, you cannot store an IP addresses in a numeric field. This is because a numeric field supports only a single decimal point (.), whereas an IP address contains three periods (.). This means you must store the addresses in a text field.

Because you store IP addresses in a text field, you cannot use the built-in sort buttons in Access to sort the addresses in a meaningful way. The sort buttons always sort values in a text field in alphabetical order, even if the characters are numbers. In other words, the addresses are sorted by the first digit, then by the second digit, and so on, instead of by the numeric values that make up the address. The following table shows the addresses from the previous table sorted in alphabetical order on the IPAddress field.

The addresses that start with 1 are displayed before the addresses that start with 2, and so on. The following table shows the addresses in the correct ascending order.

To make it easier to understand the steps involved in sorting these addresses, you break the IP addresses into four numeric parts. The addresses must be sorted by the first part, then for each value in the first part, then by the second part, and so on. The table shows each part in a different column and, because the columns hold simple numeric values, it becomes possible to sort the columns from left to right in ascending order, as shown in the following table.

Sorting the four parts separately is the trick behind sorting IP addresses. In the procedure that follows, you create a query that, in addition to the IP address field, includes four calculated columns where each column stores a part of the address values. The first calculated column will hold the first numeric part of the address, the second calculated column will hold the second numeric part of the address, and so on. Instead of sorting the records by the IPAddress field, the query will sort the records by the four calculated columns.

Create the query

You will create a select query named Sorted IPAddresses that displays the records in ascending order of IP addresses. Assume that the current database has a table named MachineDetails that includes a text field named IPAddress.

1. Click Create > Query Design. 

2. Select Add Tables (Show Table in Access) and drag Machine Details to the upper section of Design View.

3. Drag the MachineID and IPAddress fields to the query design grid.

4. You are now ready to add the calculated columns. In the first blank column on the right, type the expression PartI: Val(Left([IPAddress],(InStr(1,[IPAddress],".")-1))) in the Field row. The expression returns the characters preceding the first period (.) in the IPAddress field.

   Now, review the expression. Because you don't know how many digits make up the first part of the address, you use the InStr function to find the position of the first period. Subtracting 1 (to exclude the period) returns the number of digits in the first part. You then use this number with the Left function to extract that many characters, starting from the leftmost character, from the IPAddress field. Finally, you call the Val function to convert the characters returned by the Left function into a number. This last step is necessary because the underlying data type is Text.

5. Save the query by clicking Save on the Quick Access Toolbar. It is a good idea to do this after each intermediate step.

6. Add the column for the second address part. In the column to the right of PartI, type PartII: Val(Mid([IPAddress],InStr(1,[IPAddress],".")+1,InStr(InStr(1,[IPAddress],".")+1,[IPAddress],".")-InStr(1,[IPAddress],".")-1)) in the Field row. The expression returns the characters that are located between the first and second periods in the IPAddress field.

   Again, review the expression. Because you don't know how many digits make up the second part of the address or exactly where the second part starts (because you don't how long the first part is), you use the InStr function to find the positions of the periods. You then use the Mid function to extract the characters that follow the first period but precede the second period. Finally, you call the Val function to convert the characters returned by the Mid function into a number. This last step is necessary because the underlying data type is Text.

7. Add the column for the third address part. In the column to the right of PartII, type PartIII: Val(Mid([IPAddress],InStr(InStr(1,[IPAddress],".")+1,[IPAddress],".")+1,InStr(InStr(InStr(1,[IPAddress],".")+1,[IPAddress],".")+1,[IPAddress],".")-InStr(InStr(1,[IPAddress],".")+1,[IPAddress],".")-1)) in the Field row. The expression returns the characters that are located between the second and third periods in the IPAddress field.

   Again, review the expression. Because you don't know how many digits make up the third part of the address or exactly where the third part starts (because you don't know how long the first and second parts are), you use the InStr function to find the positions of the periods. You then use the Mid function to extract the characters that follow the second period but precede the third period. Finally, you call the Val function to convert the characters returned by the Mid function into a number. This last step is necessary because the underlying data type is Text.

8. Add the column for the fourth and final address part. In the column to the right of PartIII, type PartIV: Val(Right([IPAddress],Len([IPAddress])-InStr(InStr(InStr(1,[IPAddress],".")+1,[IPAddress],".")+1,[IPAddress],"."))) in the Field row. The expression returns the characters that follow the last period.

   Again, review the expression. The key is to find the location of the third period and then extract all the characters that follow it. Because you don't know exactly where the third period is located, you call the InStr function three times to find the position of the third period. You then use the Len function to calculate the number of digits in the fourth part. The number of digits returned is then used with the Right function to extract that many characters from the right portion of the IPAddress field. Finally, you call the Val function to convert the characters returned by the Mid function into a number. This last step is necessary because the underlying data type is Text.

9. Set the Sort row of all four calculated columns to Ascending.

   Important    The Sort row of the IPAddress column must be left blank.

   If you want to sort by other field values, in addition to the addresses, place the fields either to the left or right of all four calculated columns. Do not place the other sort fields between the calculated columns.

10. The next step is to hide the four calculated columns from the datasheet. But before you do that, switch to Datasheet view to see the result of the expressions in the calculated columns. The following table shows the columns you will see in Datasheet view.

    MachineID	IPAddress	PartI	PartII	PartIII	PartIV
    3	1.198.3.93	1	198	3	93
    4	32.183.93.40	32	183	93	40
    5	104.30.244.2	104	30	244	2
    6	104.244.4.1	104	244	4	1
    2	104.244.253.29	104	244	253	29
    1	123.4.245.23	123	4	245	23

11. Switch back to Design view and clear the check box in the Show row of all four calculated columns. This prevents the calculated columns from being displayed in Datasheet view.

12. Optionally, specify criteria for excluding records from the query.

13. Switch to Datasheet view to see the records in sorted order. You will see the records correctly sorted in ascending order of the IP addresses.

More uses for sorted IP addresses