sorting IP Addresses in Access

[Gary Walter]

Mr. Ben-Gan,

I was thinking about posting the
following on access ng but wanted
to get your permission first.

Would this be okay?

Gary Walter

///////////////////////////
In March 2005 issue of "SQL Server magazine"
Mr. Ben-Gan looks at sorting IP Addresses.

One ingeneous method involves creating an "IPPattern" table.
I thought I would see how this worked in Access.

I created a form in a temp db and added 5 command
buttons:
cmdCreateFillIP
cmdCreateFillNums
cmdCreateIPPatterns
cmdWrongSort
cmdCorrectSort

cmdWrongSort opens a query
where ip's are sorted like text:
ip
192.168.11.10
192.168.3.19
192.9.1.55
22.1.164.22
22.212.8.39
22.76.32.47
3.77.202.225
3.77.202.6
3.8.137.98

cmdCorrectSort opens a query
where each subnet is sorted as a number:
ip
3.8.137.98
3.77.202.6
3.77.202.225
22.1.164.22
22.76.32.47
22.212.8.39
192.9.1.55
192.168.3.19
192.168.11.10

here be the code behind the form (command buttons):

Option Compare Database
Option Explicit
Private Sub cmdCreateFillIP_Click()
Dim strSQL As String
strSQL = "CREATE TABLE IPs (ip TEXT(15) NOT NULL PRIMARY KEY)"
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "INSERT INTO IPs VALUES('192.168.11.10')"
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO IPs VALUES('3.77.202.225')"
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO IPs VALUES('22.1.164.22')"
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO IPs VALUES('3.8.137.98')"
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO IPs VALUES('192.9.1.55')"
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO IPs VALUES('3.77.202.6')"
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO IPs VALUES('22.76.32.47')"
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO IPs VALUES('192.168.3.19')"
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO IPs VALUES('22.212.8.39')"
CurrentDb.Execute strSQL, dbFailOnError

MsgBox "Successfully created and filled table 'IPs'"
End Sub
Private Sub cmdCreateFillNums_Click()
Dim strSQL As String
strSQL = "CREATE TABLE Nums (n INTEGER NOT NULL PRIMARY KEY)"
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "INSERT INTO Nums VALUES(1)"
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO Nums VALUES(2)"
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO Nums VALUES(3)"
CurrentDb.Execute strSQL, dbFailOnError

MsgBox "Successfully created and filled table 'Nums'"
End Sub
Private Sub cmdCreateIPPatterns_Click()
Dim strSQL As String
strSQL = "SELECT Choose(N1.n,'#','##','###') & '.' & " _
& "Choose(N2.n,'#','##','###') & '.' & " _
& "Choose(N3.n,'#','##','###') & '.' & " _
& "Choose(N4.n,'#','##','###') AS pattern, " _
& "1 AS S1, " _
& "N1.n AS L1, " _
& "N1.n+2 AS S2, " _
& "N2.n AS L2, " _
& "N1.n+N2.n+3 AS S3, " _
& "N3.n AS L3, " _
& "N1.n+N2.n+N3.n+4 AS S4, " _
& "N4.n AS L4 " _
& "INTO IPPatterns " _
& "FROM Nums AS N1, Nums AS N2, Nums AS N3, Nums AS N4;"
CurrentDb.Execute strSQL, dbFailOnError


MsgBox "Successfully created table 'IPPatterns'"

End Sub

Private Sub cmdWrongSort_Click()
On Error GoTo Err_cmdWrongSort_Click
Dim strqname As String
Dim strSQL As String
Dim qdf As DAO.QueryDef

strSQL = "SELECT * FROM IPs ORDER BY ip"
strqname = "TMP" & Now()
Set qdf = CurrentDb.CreateQueryDef(strqname)
qdf.SQL = strSQL
DoCmd.OpenQuery strqname
qdf.Close

Exit_cmdWrongSort_Click:
Set qdf = Nothing
Exit Sub

Err_cmdWrongSort_Click:
MsgBox Err.Description
Resume Exit_cmdWrongSort_Click

End Sub
Private Sub cmdCorrectSort_Click()
On Error GoTo Err_cmdCorrectSort_Click
Dim strqname As String
Dim strSQL As String
Dim qdf As DAO.QueryDef

strSQL = "SELECT ip FROM IPs INNER JOIN IPPatterns " _
& "ON IPs.ip LIKE IPPatterns.pattern " _
& "ORDER BY " _
& "CLng(Mid(IPs.ip,IPPatterns.S1,IPPatterns.L1)), " _
& "CLng(Mid(IPs.ip,IPPatterns.S2,IPPatterns.L2)), " _
& "CLng(Mid(IPs.ip,IPPatterns.S3,IPPatterns.L3)), " _
& "Clng(Mid(IPs.ip,IPPatterns.S4,IPPatterns.L4));"
'Debug.Print strSQL
strqname = "TMP" & Now()
Set qdf = CurrentDb.CreateQueryDef(strqname)
qdf.SQL = strSQL
DoCmd.OpenQuery strqname
qdf.Close

Exit_cmdCorrectSort_Click:
Set qdf = Nothing
Exit Sub

Err_cmdCorrectSort_Click:
MsgBox Err.Description
Resume Exit_cmdCorrectSort_Click
End Sub