custom sorting

[fcinelli]

Hi all,

I am having a problem sorting a field that contains alphanumeric
values. The field contains values such as:
10.1, 10.2, 10.1.2, 10.2.1, 9.1, 3.1, 5.4 etc... This is the naming
convention my organization is using to identify departments so there
is no way around it.

Because some values have 2 decimals, I have to make this a text field
which messes up the sorting (it puts 10 above 9). I need to be able
to sort by proper numeric order. So the proper order might be
something like:

2.1
2.2
2.2.1
2.2.2
2.3
2.4
....
12.1
12.2
12.2.1
12.2.1.1
....
etc

I've created a field to represent a sorting key (number type field)
but I know it will complicate things later on when the user adds a new
record...the sorting key would no longer be valid because there would
be a cascading effect.

Does anyone have any advice on a procedure that will automate the
process of assigning sorting keys to each record?

Thanks

 

[James A. Fortune]

Hi all,

I am having a problem sorting a field that contains alphanumeric
values. The field contains values such as:
10.1, 10.2, 10.1.2, 10.2.1, 9.1, 3.1, 5.4 etc... This is the naming
convention my organization is using to identify departments so there
is no way around it.

Because some values have 2 decimals, I have to make this a text field
which messes up the sorting (it puts 10 above 9). I need to be able
to sort by proper numeric order. So the proper order might be
something like:

2.1
2.2
2.2.1
2.2.2
2.3
2.4
...
12.1
12.2
12.2.1
12.2.1.1
...
etc

I've created a field to represent a sorting key (number type field)
but I know it will complicate things later on when the user adds a new
record...the sorting key would no longer be valid because there would
be a cascading effect.

Does anyone have any advice on a procedure that will automate the
process of assigning sorting keys to each record?

Thanks

Maybe

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/8dfb7c5e5bc0791f

James A. Fortune
(e-mail address removed)

 

[Ron2006]

What I meant was that on the form where you let them change the
department (what I called the WBS), in the afterupdate event on that
form. pass the new wbs through the function and update the sort field
right then. Now that you have all the sort fields loaded for all the
records. that will maintain the sort field, so you do not have to run
the update anymore.

The function on that form does not even have to be a function, it can
be simply part of the afterupdate event and actual field name can be
used instead.

Ron

 

[fcinelli]

What I meant was that on the form where you let them change the
department (what I called the WBS), in the afterupdate event on that
form. pass the new wbs through the function and update the sort field
right then. Now that you have all the sort fields loaded for all the
records. that will maintain the sort field, so you do not have to run
the update anymore.

The function on that form does not even have to be a function, it can
be simply part of the afterupdate event and actual field name can be
used instead.

Ron

Ah I see what you mean now...and I'm sure it's more efficient than
what I did. Thanks a lot Ron!

 

[Ron2006]

Ah I see what you mean now...and I'm sure it's more efficient than
what I did. Thanks a lot Ron!

You are welcome.

Have a great day.

Now the warnings.... If they skip the dots or put a comma instead,
the result will not be correct. Just a reminder. to keep an eye on the
process, incase you end up having to put some testing logic behind it
to protect yourself.

Ron

 

[James A. Fortune]

What I meant was that on the form where you let them change the
department (what I called the WBS), in the afterupdate event on that
form. pass the new wbs through the function and update the sort field
right then. Now that you have all the sort fields loaded for all the
records. that will maintain the sort field, so you do not have to run
the update anymore.

The function on that form does not even have to be a function, it can
be simply part of the afterupdate event and actual field name can be
used instead.

Ron

The method I cited automatically sorts the numbers into the proper sort
order by department but at the cost of some processing time. It seems
like you are setting a sorting whenever a record is edited or added. If
I understand correctly, that's another way to do it and would save time
at the expense of computing the sort at the time of record creation or
editing. Putting a calculated field into a table is O.K. IMO if you
can't live with the processing time. It would be good to run a test
first to see the kind of time we're talking about.

James A. Fortune
(e-mail address removed)