Sorting in an Access Query

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

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
this to sort correctly.
 
Nick said:
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
this to sort correctly.
Click to expand...

Hi Nick,

You have several options.

1) Store each subnet in a separate number field.

This is the "best" alternative I believe and can make
sorting a snap.

2) Use a function to return each subnet as a number in separate fields
in your query, then sort by these fields.

Respond back if you would like a function for this.

3) Pad each subnet with zeroes in a sorting field
that you *never* show to the user
("126.000.000.005" will resolve to a different
IP address than "126.0.0.5").

Here be a function that will do the left-padding
(from Fred Kerr on Access-L list):

Public Function PadIP(strIP as String) As String
Dim i As Integer, a As Variant
'split IP address into separate segments
a = Split(strIP, ".")
For i = 0 To UBound(a)
'make sure each IP segment is 3 digits
'by padding left side with zeroes
a(i) = Right("00" & a(i), 3)
Next
'recombine padded segments
' 129.93.16.24 goes to 129.093.016.024
PadIP = Join(a, ".")
End Function

Save this function in a module,
then in your query add

SELECT ...
FROM ....
ORDER BY PadIP([yourIPfield]);

If you cannot guarantee that IP field will not contain
any Nulls, you might add NZ

SELECT ...
FROM ....
ORDER BY PadIP(Nz([yourIPfield],"0.0.0.0"));




Please respond back if I was not clear about something.

Good luck,

Gary Walter
 
-----Original Message-----

Nick said:
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
this to sort correctly.
Click to expand...

Hi Nick,

You have several options.

1) Store each subnet in a separate number field.

This is the "best" alternative I believe and can make
sorting a snap.

2) Use a function to return each subnet as a number in separate fields
in your query, then sort by these fields.

Respond back if you would like a function for this.

3) Pad each subnet with zeroes in a sorting field
that you *never* show to the user
("126.000.000.005" will resolve to a different
IP address than "126.0.0.5").

Here be a function that will do the left-padding
(from Fred Kerr on Access-L list):

Public Function PadIP(strIP as String) As String
Dim i As Integer, a As Variant
'split IP address into separate segments
a = Split(strIP, ".")
For i = 0 To UBound(a)
'make sure each IP segment is 3 digits
'by padding left side with zeroes
a(i) = Right("00" & a(i), 3)
Next
'recombine padded segments
' 129.93.16.24 goes to 129.093.016.024
PadIP = Join(a, ".")
End Function

Save this function in a module,
then in your query add

SELECT ...
FROM ....
ORDER BY PadIP([yourIPfield]);

If you cannot guarantee that IP field will not contain
any Nulls, you might add NZ

SELECT ...
FROM ....
ORDER BY PadIP(Nz([yourIPfield],"0.0.0.0"));




Please respond back if I was not clear about something.

Good luck,

Gary Walter


.
So on 3 are you saying I need to go into my database and
Click to expand...
add 0's on each subnet on every IP?

Also I would like the function for #2
 
So on 3 are you saying I need to go into my database and
add 0's on each subnet on every IP?
Click to expand...
NO, I *tried* to say exactly the opposite.

Do NOT pad subnets with 0's in the
field in your table!!!!!

When you want to sort by IP in your query,
then in your "ORDER BY" clause,
use the function on your IP field...as in....

ORDER BY PadIP(Nz([yourIPfield],"0.0.0.0"))
 
Back
Top