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

 

[Jeff Boyce]

Assuming the "." separates sub- and sub-sub- departments, another approach
would be to NOT stuff all those facts into the same field.

If you use three fields (or however many "deep" you need), you can sort by
all three, and still use numbers and sort numerically (but by all three, not
just one).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Ron2006]

Another possible is with a not shown sort key that is loaded in the
afterupdate for the WBS field that you have.

Here is some code that assums that the max number between dots is 999.

put it where you can execute it or change it to work just on that form
by using the form field names.

===================================
Function createsortkey(InWBS, outsortKey)
Dim BasetoBe As String
Dim startpos As Integer
Dim currpos As Integer
Dim maxpos As Integer
Dim nxtDot As Integer
Dim lstDot As Integer
Dim hldwbs As String
Dim finalWBS As String


BasetoBe = "000"
startpos = 1
currpos = 0
maxpos = Len(InWBS)
hldwbs = ""
lstDot = 0

Do While maxpos > currpos - 1
nxtDot = InStr(startpos, InWBS, ".")
If nxtDot = 0 Then
nxtDot = maxpos + 1
End If
BasetoBe = Left(BasetoBe, 3 - (nxtDot - (lstDot + 1))) & Mid(InWBS,
lstDot + 1, (nxtDot - (lstDot + 1)))
finalWBS = finalWBS & BasetoBe & "."
lstDot = nxtDot
BasetoBe = "000."
currpos = nxtDot + 1
startpos = nxtDot + 1
' MsgBox finalWBS
Loop
outsortKey = finalWBS

End Function
=====================================

Ron

 

[fcinelli]

Thanks for your input Ron, but I don't understand how your function
stores the sort key. I need to be able to use this sort key later on
for sorting queries and reports. I am creating a form that will allow
the user to edit/add/remove department info, then afterwards when all
changes are complete, I can use your function to convert the ID into
text that can be sorted in the proper order I need.

I guess I can call your function and pass every department ID into it
and then update my sort key field in the table...
Do you think that will work or is a bad idea?

 

[fcinelli]

I passed every dept ID into your function then ran an UPDATE query to
update the table...worked well. It's probably not the most efficient
way but it is good enough. Thanks Ron.

 

[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)