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

 

[SkipVought]

hi,

Your data is TEXT. Confert to NUMEIC using a function like CInt().

Skip,

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

 

[hoofit]

Hi Skip,
I see. Does changing the data type in the table to number have any ill effects?

Thank you

 

[MajP]

you do not have to change the table. You can add a calculated field to your query.
LongValue:clng([YourFieldName])

 

[hoofit]

MajP,\

I ran a quick test and the sort is correct using my test field name "number" as follows.
longvalue: CLng([number])


The field name in the query that this will apply to is
"categoryCodeMName" so I tried...
longvalue:clng([categorycodeMName])and got a bunch of errors. Any followup?

Thank you

hoof

 

[PHV]

I'm sorting data
In SQL change your ORDER BY clause from this:
ORDER BY [some field]
to this:
ORDER BY Val([some field])

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

 

[MajP]

you probably want to roll your own function to handle nulls, and other "bad" values.

Public Function myCDbl(varVal As Variant) As Double
  If IsNumeric(varVal) Then
    myCDbl = CDbl(varVal)
  End If
End Function

This will not bomb out on bad data.

 

[hoofit]

PHV
Here's what I've got.

SELECT PM.PMName AS categorycode, PM.PMName AS description
FROM PM
ORDER BY PM.PMName;

I tried;

SELECT PM.PMName AS categorycode, PM.PMName AS description
FROM PM
ORDER BY val ([PM.PMName;])

and encountered errors.

 

[PHV]

SELECT PMName AS categorycode, PMName AS description
FROM PM
ORDER BY Val(PMName)

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

 

[MajP]

Again, if PMName is Null this Val(PMName) crashes.

 

[MajP]

if you do not want a UDF then at least.
val(NZ(PMname,0))

 

[PHV]

MajP, you're right but I've assumed that no one will load a treeview with Null values ...

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

 

[MajP]

When he stated his query was getting a bunch of errors I assumed his data is bad and val was failing. Was not a whole lot of info to go on, since he did not describe the errors.

 

[MajP]

BTW, if you are planning to use this field as the key for the node you need to ensure that the field is unique and no nulls. Also the key field is a string. And do not ask me why, but you can not use a string that resolves to a numeric. Not sure if that is a bug or a feature. In other words if X = "100" then key: = X will not work. Always concatenate with an alphabetic character. Something like key:= "ID" & X.

 

[hoofit]

MajP,
Thanx for the foresight. I think a minor strategy change is in order. This will actually be part of a composite key and I'm considering just leaving it as a text field and code for nulls. PHV, I need to agree with MajP. I think that this would best prevent problems in the future. But to both, can I not simply change the SQL and prevent nulls?

hoof

 

[PHV]

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

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

 

[hoofit]

PHV,
When I run the query I have;

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

The values are in order however I cannot change it from descending to ascending....

 

[MajP]

I would shy away from a composite key when working with a treeview (especially if it is big). It just makes working with it (depending on what you are doing) more complicated and the code longer. If I can, I always base my treeviews off a query with a simple autonumber PK. I always want to tie my tree to the underlying data. If I click on a node I want to see that record in a form, If I drag and drop a node I want to change sort order or change relations. If I click a node and pop up a form I want to make edits and see them updated in the tree. So my nodes always have a Primary key from the corresponding record as the node key. I store foreign keys in the tag property.

 

[hoofit]

Actually, I can change between descend and ascend bu I'm back to square one with the numbers being treated as text.

 

[PHV]

I cannot change it from descending to ascending
????