Question on building a query

[Guest]

Hi -

I have a table with a text field that contains section numbers of a report,
and I would like to write a query that will sort in order by that field. The
problem is, since it's a text field it obviously doesn't sort correctly.
Some example entries are as follows:

SectionNumber
3.10.4.9.0
3.1.2.23.4
3.1.20.16.4
3.100.3.7.12

So when it's sorted, it should do it on the numerical values between the
dots, so if sorted correctly it would look like this:

SectionNumber
3.1.2.23.4
3.1.20.16.4
3.10.4.9.0
3.100.3.7.12

There can also be varying numbers of periods in that field from one record
to the next.

Thanks so much for any ideas!

Dan

 

[Douglas J. Steele]

Your best bet is to store the components as separate fields in the table.
You can concatenate them together as a computed field in your query.