Text Sort

[A. Respecte]

I have a list of IP addresses, amongst other fields in a db that I
need to sort, however, I am having difficulties accomplishing that,
and any help will be appreciated. Here is a sample list of the
entries.

It is now
10.100.1.10
10.100.1.23
10.100.1.249
10.100.1.7
10.105.1.1
10.105.1.2
10.105.1.251
10.105.1.3
10.105.1.5
10.105.1.6

It should be;
10.100.1.7
10.100.1.10
10.100.1.23
10.100.1.249
10.105.1.1
10.105.1.2
10.105.1.3
10.105.1.5
10.105.1.6
10.105.1.251

Thanks for any imput.

A. Respecte

[Allen Browne]

Consider using 4 separate fields to store the IP addresses.
Number type, size Byte.

Easy to concatenate them with dots for display purposes (e.g. on a report.)

I think that will be much more efficient than:
ORDER BY IIf([MyField] Is Null, Null, Val(Split([MyField], ".")(0)),
Val(Split([MyField], ".")(1)), Val(Split([MyField], ".")(2)),
Val(Split([MyField], ".")(3)))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"A. Respecte" <re******@yahoo.com> wrote in message
news:58**************************@posting.google.c om...

I have a list of IP addresses, amongst other fields in a db that I
need to sort, however, I am having difficulties accomplishing that,
and any help will be appreciated. Here is a sample list of the
entries.

It is now
10.100.1.10
10.100.1.23
10.100.1.249
10.100.1.7
10.105.1.1
10.105.1.2
10.105.1.251
10.105.1.3
10.105.1.5
10.105.1.6

It should be;
10.100.1.7
10.100.1.10
10.100.1.23
10.100.1.249
10.105.1.1
10.105.1.2
10.105.1.3
10.105.1.5
10.105.1.6
10.105.1.251

Thanks for any imput.

A. Respecte

[Phil Stanton]

Can't guarantee it will work, but OK on sample data

Assume input table is table 1 and the IP addresses are in a field called
"Input"
Create Query 1

SELECT Table1.Input, InStr([Input],".") AS PosFirst,
CInt(Left([Input],InStr([Input],".")-1)) AS [First],
Right([Input],Len([Input])-Len([First])-1) AS Field2,
CInt(Left([Field2],InStr([Field2],".")-1)) AS [Second],
Right([Field2],Len([Field2])-Len([Second])-1) AS Field3,
CInt(Left([Field3],InStr([Field3],".")-1)) AS Third,
CInt(Right([Field3],Len([Field3])-Len([Third])-1)) AS Forth
FROM Table1;
Create Query 2

SELECT Query1.First, Query1.Second, Query1.Third, Query1.Forth,
CStr([First]) & "." & CStr([Second]) & "." & CStr([Third]) & "." &
CStr([Forth]) AS [Output]
FROM Query1
ORDER BY Query1.First, Query1.Second, Query1.Third, Query1.Forth;

HTH

Phil
"A. Respecte" <re******@yahoo.com> wrote in message
news:58**************************@posting.google.c om...

I have a list of IP addresses, amongst other fields in a db that I
need to sort, however, I am having difficulties accomplishing that,
and any help will be appreciated. Here is a sample list of the
entries.

It is now
10.100.1.10
10.100.1.23
10.100.1.249
10.100.1.7
10.105.1.1
10.105.1.2
10.105.1.251
10.105.1.3
10.105.1.5
10.105.1.6

It should be;
10.100.1.7
10.100.1.10
10.100.1.23
10.100.1.249
10.105.1.1
10.105.1.2
10.105.1.3
10.105.1.5
10.105.1.6
10.105.1.251

Thanks for any imput.

A. Respecte

[A. Respecte]

Thanks Phil and Allen for both of your input. I tried both, but due
to the no of records in the database, and ease of maintenance, Phil's
query solution worked better and easier. I have been able to generate
the reports I need based on it. Again thanks for the fast responses.
-A. Respecte

"Phil Stanton" <di********@stantonfamily.co.uk> wrote in message news:<41***********************@mercury.nildram.ne t>...

Can't guarantee it will work, but OK on sample data

Assume input table is table 1 and the IP addresses are in a field called
"Input"
Create Query 1

SELECT Table1.Input, InStr([Input],".") AS PosFirst,
CInt(Left([Input],InStr([Input],".")-1)) AS [First],
Right([Input],Len([Input])-Len([First])-1) AS Field2,
CInt(Left([Field2],InStr([Field2],".")-1)) AS [Second],
Right([Field2],Len([Field2])-Len([Second])-1) AS Field3,
CInt(Left([Field3],InStr([Field3],".")-1)) AS Third,
CInt(Right([Field3],Len([Field3])-Len([Third])-1)) AS Forth
FROM Table1;
Create Query 2

SELECT Query1.First, Query1.Second, Query1.Third, Query1.Forth,
CStr([First]) & "." & CStr([Second]) & "." & CStr([Third]) & "." &
CStr([Forth]) AS [Output]
FROM Query1
ORDER BY Query1.First, Query1.Second, Query1.Third, Query1.Forth;

HTH

Phil
"A. Respecte" <re******@yahoo.com> wrote in message
news:58**************************@posting.google.c om...

I have a list of IP addresses, amongst other fields in a db that I
need to sort, however, I am having difficulties accomplishing that,
and any help will be appreciated. Here is a sample list of the
entries.

It is now
10.100.1.10
10.100.1.23
10.100.1.249
10.100.1.7
10.105.1.1
10.105.1.2
10.105.1.251
10.105.1.3
10.105.1.5
10.105.1.6

It should be;
10.100.1.7
10.100.1.10
10.100.1.23
10.100.1.249
10.105.1.1
10.105.1.2
10.105.1.3
10.105.1.5
10.105.1.6
10.105.1.251

Thanks for any imput.

A. Respecte