Sorting 1

[hoofit]

Hello all,
I'm sorting data for a tree control using the following values;
10
100
125
175
50

How is the sort done so the entries are in numerical order;
10
50
100
125
175

Thank you

 

[hoofit]

MajP,
Yes I understand but I've already got it near completion. The structure is complete. I need to make a decision about text or numeric data that will feed it.

 

[hoofit]

The numbers are not in numerical order PHV, when I change between ascend and descend

 

[PHV]

when I change between ascend and descend
When you change what ? where ? how ?

 

[hoofit]

In the query, I can change the sort order to either ascend, descend or no sort and end up withe the same issue as in the first post.

 

[MajP]

please post your final sql.

 

[hoofit]

SELECT PM.PMName AS categorycode, PM.PMName AS description
FROM PM
WHERE (((PM.PMName) Is Not Null))
ORDER BY PM.PMName;

Values are preceeded by "PM",
this may be the problem.
This is the order I have
PM10
PM100
PM125
PM175
PM50

This is the order I need
PM10
PM50
PM100
PM125
PM175

hoof

 

[MajP]

SELECT PM.pmName
..other fields
FROM PM
WHERE ((Not (PM.pmName) Is Null))
ORDER BY Val(Mid([pmName],3));

 

[hoofit]

MajP,
The master has spoken! It works. Hail to the Chief. So we're handling nulls. Could you briefly elaborate on the last line.

SELECT PM.PMName AS categorycode, PM.PMName AS description
FROM PM
WHERE ((Not (PM.PMName) Is Null))
ORDER BY Val(Mid([pmName],3));

hoof

 

[MajP]

The only reason no one else figured it sooner was you kind of failed to mention a real big thing:

Values are preceeded by "PM",
this may be the problem

That is a big difference from the original posts.

The mid function is a vb function

http://www.techonthenet.com/access/functions/string/mid.php

It returns a string after the starting at the 3rd character
pm100
becomes
100

then the val turns the 100 from a string to a number

 

[hoofit]

MajP,

Uh-Oh. Something tells me that you put it nicely. Thank you so much for the solution, I'll read up on that function, that's a handy tool. PHV, thanx for jumping in and Skip as well early on.

hoof

 

[hoofit]

Just a final note. Having the sort issue resolved I found that even though the sorted data produced by the query(s) was fed to the tree control, it was not sorted in the control. I set the sort property of the tree control to 'not sorted' thus resolving that issue. Strange. Thank you again.

hoof

 

[MajP]

The sorted property of the tree view sorts the displayed node text. The displayed text is textual and thus you are back to a text wise sort.

 

[SkipVought]

Chnage the Node Names...

FROM
[tt]
PM10
PM100
PM125
PM175
PM50
[/tt]
TO
[tt]
PM010
PM050
PM100
PM125
PM175
[/tt]
for an expected TEXT sort.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

Skip no need to do that. If his query is properly sorted then he is likely adding the nodes in the order he wants them. As long as he does not set the property of the treeview to sorted then it will show in the order he enters the nodes.

However, you are correct if he is going to dynamically enter more nodes. With your method e would be able to have a node be in the correct location without the need of reloading the tree or figuring out the correct sibling.

 

[PHV]

Why not using this ?
SELECT Left(PMName,2) & Format(Val(Mid(PMName,3)),"0000") AS categorycode, PMName AS description
FROM PM
WHERE PMName Is Not Null
ORDER BY 1;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hoofit]

My plan is to have an initial setup, demo if you will, for the user of how to arrange PM's. The user may simply choose to use my setup or modify it to suit their own need. Thus, data entry would be dynamic.

hoof

 

[MajP]

Then I would suggest that you sort the tree based on the underlying query and do not try to use the tree sort property. This way you have more flexibility and not tied to sorting on the node text.