Sort using text field with numbers and periods

[dave996]

HELP PLEASE!!!!



I have a table with 3 main fields, ID as Autonumber, Clause_No as Text and Description as Text.

I need to sort based on the clause_no field however. this is a text field which includes numbers only but seperated by full stops:

1.1
1.2
1.3
1.3.1
1.3.2
1.10
and so on...

On a normal sort I am getting:

1.1
1.10
1.2
1.3
1.3.1
1.3.2

Having tried replacing the period, attempts at different formatting and use of Val() and trying to nake a new field with some sort of logic has proved unsuccesful so far.
I am unable to use the ID field as additional clauses can be added in the future which may need to sit between exiting clauses

Any ideas before I give up and add a new field and have to enter all the clause numbers in a differnt format - please??

Thanks
Dave

[orange]

You might consider adding a new field/column to your table. It's purpose would be for sorting, and named along
the lines of SeqNo or SeqFld... and would be a numeric (integer/long) data type. You could value this field with numbers incrementing by 5 or 10 to allow insertions. It does not affect your Clause_No values. It's only purpose is to accomplish the sort and provide the sequence you need.

[dave996]

Thanks for the idea - that was going to be my final option.
I just wondered if there was a way of 'automating' the process rather than this extra need to 'manual intervention' when adding new clauses

Cheers
Dave

[orange]

What exactly do you mean by "manual intervention"?

When you need to show your data in the Clause_no sequence, you include the SeqNo field for sort only

SELECT Clause_no, Description... from YourTable
ORDER BY SeqNo;

When you have to insert a clause between existing clauses, you could do that on a form perhaps with a parameter to identify where that clause should fit relative to existing records. You can always renumber/revalue the SeqNo if needed. Or you can assign the SeqNo based on existing values (that's why I suggested the original values be 5,10,15 etc to allow for inserts).

Knowing where the new Clause should fit would be human decision regardless of the SeqNo field being added.

[dave996]

I mean that when a new clause was added, the 'SeqNo' field would have to be maually updated by the user so it had a suitable value ensuring it appeared in then right order rather than this being able to be calculated base don the actual clause no.
Dave

[orange]

Ok. The user would enter the clause number in your current set up, so it would be an extra field to complete. But it will give the sort order you need.

Here's another option to try (attached my mock up below) showing Table, then query

SELECT Article.PubDate, Article.productid, Article.Colour, Article.NoOfClips, Article.ClauseNo
FROM Article
ORDER BY  round(CDbl([clauseno]),3) ;

You can change the 3 to the max number of sub clauses. This will not work if you have multiple "." in the clause_no.

[dave996]

I think Ive managed to get a solution - allbeit a little clunky....

I have made a new field in the table called [New_Clause_No] and put an input mask on the original [Clause_No] field: 99.99.99.99.99.99.99.99.99.99.99;0;-
I enter all clause numbers into the [CLause_No] field as pairs of 2 digits for every level i.e. 01.01.02 etc. (Fortunately it will never go above 99 in each pair).


From this I have a routine that creates a new variable limiting the length to a valid string using: Left([clause],InStr([clause],".."))
This then evaluates each pair of numbers between the "." and if there is a leading 0, removes it
The final step is to concaternate what is left from the last step and form a new value in a New_Clause_No field.

For reports, I use the [New_Clause_No] field as the clause number and the original [Clause_No] field as the sort field

As I say, a little clunky but it works for what I need.

Dave

[dave996]

This is what Ive come up with....

[orange]

Good stuff. If it works for what you need, that's great.

[dave996]

Thanks very much for your ideas

[Bullschmidt]

I need to sort based on the clause_no field however. this is a text field which includes numbers only but seperated by full stops:

1.1
1.2
1.3
1.3.1
1.3.2
1.10
and so on...

On a normal sort I am getting:

1.1
1.10
1.2
1.3
1.3.1
1.3.2

And here's my stab at it...

The data looks kind of like the Dewey Decimal System used in libraries.

Tested and at least with the above limited data this worked sorting on a calculated SortCalc field in the query:

SortCalc: Eval(Replace([clause_no],".","+.001*"))

So the Replace() function above converts the initial data to be the following strings:
1+.001*1
1+.001*2
1+.001*3
1+.001*3+.001*1
1+.001*3+.001*2
1+.001*10

And then the Eval() function converts the above strings to be the following numbers:
1.001
1.002
1.003
1.004
1.005
1.01

[Bullschmidt]

Upon further thought i realized that my last post is not correct - would only generally work for the first two levels...