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]

Re: Text Sort

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" <respecte@yahoo .com> wrote in message
news:58914f32.0 409270537.6ef2f 225@posting.goo gle.com...[color=blue]
>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[/color]

[Phil Stanton]

Re: Text Sort

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" <respecte@yahoo .com> wrote in message
news:58914f32.0 409270537.6ef2f 225@posting.goo gle.com...[color=blue]
>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[/color]

[A. Respecte]

Re: Text Sort

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" <discussion@sta ntonfamily.co.u k> wrote in message news:<4158207b$ 0$52214$65c6931 4@mercury.nildr am.net>...[color=blue]
> 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" <respecte@yahoo .com> wrote in message
> news:58914f32.0 409270537.6ef2f 225@posting.goo gle.com...[color=green]
> >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[/color][/color]