Sort IP addresses in an Access database

This topic describes the procedure for sorting Internet Protocol (IP) addresses stored in a Microsoft Access database.

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.

MachineID Employee Location IPAddress
1 ... ... 123.4.245.23
2 ... ... 104.244.253.29
3 ... ... 1.198.3.93
4 ... ... 32.183.93.40
5 ... ... 104.30.244.2
6 ... ... 104.244.4.1

The reason IP addresses are challenging to work with is that Access does not provide a special data type for storing IP addresses. And even though the address is just a collection of numbers, you cannot store the 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 leaves you with only the option of storing the addresses in a text field.

Storing IP addresses in a text field means that you cannot use the sort buttons on the toolbar 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.

MachineID IPAddress
3 1.198.3.93
2 104.244.253.29
6 104.244.4.1
5 104.30.244.2
1 123.4.245.23
4 32.183.93.40

You see all addresses that start with "1" before you see addresses that start with 2, and so on. The following table shows the addresses in the correct ascending order.

MachineID IPAddress
3 1.198.3.93
4 32.183.93.40
5 104.30.244.2
6 104.244.4.1
2 104.244.253.29
1 123.4.245.23

To make it easier to understand the steps involved in sorting these addresses, let's break the 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.

PartI PartII PartIII PartIV
1 198 3 93
32 183 93 40
104 30 244 2
104 244 4 1
104 244 253 29
123 4 245 23

Sorting the four parts separately is the trick behind sorting IP addresses. In the procedure that follows, you will 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.

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

  1. In the Database window, click Queries under Objects, and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Simple Query Wizard, and then click OK.
  3. In the Tables/Queries list, click the MachineDetails table that contains the IPAddress field.
  4. Under Available Fields, double-click the MachineID and IPAddress fields, and then click Next.
  5. Name the query Sorted IPAddresses, and then click Modify the query design.
  6. Click Finish.
  7. The query will open in Design view. You will see the MachineID and IPAddress fields in the design grid.

Fields, including the IPAddress field, in the design grid

  1. 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.

Let's review the expression. Because we don't know how many digits make up the first part of the address, we 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. We then use this number with the Left Function to extract that many characters, starting from the leftmost character, from the IPAddress field. Finally, we 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.

  1. 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.

Let's review the expression. Because we don't know how many digits make up the second part of the address or exactly where the second part starts (because we don't how long the first part is), we use the InStr function to find the positions of the periods. We then use the Mid Function to extract the characters that follow the first period but precede the second period. Finally, we 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.

  1. 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.

Let's review the expression. Because we don't know how many digits make up the third part of the address or exactly where the third part starts (because we don't know how long the first and second parts are), we use the InStr function to find the positions of the periods. We then use the Mid function to extract the characters that follow the second period but precede the third period. Finally, we 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.

  1. 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.

Let's review the expression. The key is to find the location of the third period and then extract all the characters that follow it. Because we don't know exactly where the third period is located, we call the InStr function three times to find the position of the third period. Then we use the Len Function to calculate the number of digits in the fourth part. The number of digits is then used with the Right Function to extract that many characters from the right of the IPAddress field. Finally, we 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.

  1. 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.

  1. 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. On the View menu, click Datasheet View. 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
  1. On the View menu, click Design View. Clear the check box in the Show row of all four calculated columns. This prevents the calculated columns from being displayed in Datasheet view.

Columns that contain the numeric parts of the IP addresses

  1. Optionally, specify criteria for excluding records from the query.
  2. On the View menu, click Datasheet View to see the records in sorted order. You will see the records correctly sorted in ascending order of the IP addresses.

Need to do more with sorted IP addresses?

Validate IP addresses during data entry    

If you want to validate addresses without writing code, you can do so to a limited extent by setting the InputMask property of the field to ###.###.###.###;0;" " and the Format property of the IP address field to &&&&&&&&&&&&.

What does the input mask do? When you starting typing in the address field, the input mask prevents you from entering characters other than numbers and spaces between the three periods. If a numeric part is a double-digit number, leave the third digit blank or type a space instead. Note that this input mask does not warn the user if the user skips typing one or more parts of the address, or types only spaces instead of a numeric value. For example, "345. .3. " would be accepted as a valid address.

What does the display format do? When you finish typing and leave the field, the display format removes the spaces in the address and displays just the numbers and periods. So, if you typed "354.35 .2 .12 ", the address is displayed as "354.35.2.12". Note that if you click inside the address, or press F2 (to enter edit mode) when the address is selected, the spaces will reappear.

Tip    If you want to copy an address, move to the previous column or control, press TAB to select the formatted address, and then click Copy on the Query Datasheet toolbar. By doing this, you will not copy the address string that contains spaces.

To filter records by partial IP addresses    

  1. In Datasheet view, on the Records menu, point to Filter
  2. Click Advanced Filter/Sort, and in the query design window, create four calculated columns as described in this topic.
  3. In the Criteria row of each such column, type a filter expression.

Sort records in a form or report by IP addresses    

If you need to create a form or a report in which the records are sorted by IP addresses, base the new object on a query that sorts addresses as previously described, instead of on the table that stores the addresses.

 
 
Applies to:
Access 2003