Access sorts wrong!

[syntaktik]

I have a field which is a lookup field with three fields attached to it. The actual data is a number, but I don't want to sort it by that number, rather by the other column'd number. But it just wont. Any ideas?

this is how it looks like:

Number Priority
6 3/8 1
6 1/2 2
6 7/8 3

but the result is always:

Number Priority
6 1/2 2
6 3/8 1
6 7/8 3

 

[jdraw]

I have a field which is a lookup field with three fields attached to it. The actual data is a number, but I don't want to sort it by that number, rather by the other column'd number. But it just wont. Any ideas?

this is how it looks like:

Number Priority
6 3/8 1
6 1/2 2
6 7/8 3

but the result is always:

Number Priority
6 1/2 2
6 3/8 1
6 7/8 3

Show us some code.

 

[LPurvis]

I agree - show code.
But your data looks something like:

Number  Priority
------  --------
6 1/2   2
6 3/8   1
6 7/8   3

is that right?
And you don't want to sort by the Priority column - but by "Number" (which shouldn't be called "Number" as a column name).

For that to be the displayed value then that must be a text column.
And you simply can't expect either VBA or SQL to interpret fractional values for you by default.
You need to evaluate the expression as if it was an equation. (And then sort on that - which is going to be a relatively inefficient process).
Rather than provide such a function here, grab one of many available such as one from, say, here.

Cheers.

 

[nealberk]

Where are you attempting to do this sort? Is it in a query? If so make sure it is the column selected for the sort and not the text column (which is what it looks like you are sorting on)

 

[syntaktik]

Thank you for all the replies! But yes, I am trying to sort by the priority column. It works properly when the number column contains letters, but when it contains numbers, it sorts by them, which I don't want. Can I somehow change the numbers to text? Or add a letter in front of the text?

 

[georgedwilkinson]

nealberk's question is important: how are you attempting to do this sort? There is really nothing anybody can tell you to help without that information, which has now been requested 4 times.

If you're just looking at the table (a big no-no), Access can put the data in any order it wants to. If you're using a query, what sort order are you using? What is the SQL of the query?

 

[syntaktik]

hm, Ii think that is my problem. I am using the built in button to sort with access 2007. But, yes, I am keeping all the information in a big table to manage all my many small lists. But that selected text will be stored in the actual table. I hope that sort of makes sense.

 

[syntaktik]

this is my code:

SELECT ListInfo.ListName, ListInfo.ListValue, ListInfo.SortOrdNum 
FROM ListInfo 
WHERE (((ListInfo.ListName)="Hat")) 
ORDER BY ListInfo.[SortOrdNum];

 

[georgedwilkinson]

I see neither "Number" nor "Priority" as a field name in your SQL. Are you sure this is the query you are looking at that is sorting wrong? If it is, why don't you explain why it is wrong again, since your first post obviously has nothing to do with this query.

Yes, we all keep data in tables. I was asking if you were looking directly at the table or were using a query. IMHO, the jury is still out on that.

If you're looking directly at the table (as suggested in post #7), STOP! Create a query like the one you posted in #8. If you're still having problems after that, then post the exact query you're running along with an exact partial data set displaying the problem.

 

[syntaktik]

uh oh. Sorry about that. I was trying to get an answer, so i sort of simplified it because i did not think posting my code would be necessary . But yes, the 'actual' values are:
ListName: this contains in what category ListValue falls into
ListValue: This is the actual information it is supposed to display, but not actuall represnet
SortOrdNum: Is the *real* number I want so i cna sort it properly.

However, instead of sorting it by SortOrdNum, it sorts by ListValue.
I hope maybe it is a bit more clear now. Since, i am completely clueless, all the other ones sort correctly, just not this one with numbers in ListValue.

 

[gemma-the-husky]

are you ACTUALLY storing the numbers as text - so its not

6.5

but it actually looks like 6 1/2

-------------
if it is TEXT then it IS sorting correctly

6 1/2 precedes
6 3/8 which precedes
6 7/8

as dictionary sorts - ie if these numbers are replaced by letters you get

fa/b
fc/h
fg/h

then its clear how the sort is working.

 

[syntaktik]

yes, haha. But I don't want to sort it by those numbers, rather by the "SortOrdNum" numbers which are "1,2,3..." not by the "ListValue" which is the 6 1/2 thing.

 

[gemma-the-husky]

is this a query for a report, by any chance?

if so a report ignores the order in a query - you have to set your own order in sorting and grouping

 

[syntaktik]

oh, this is part of a split form.

 

[georgedwilkinson]

Where's the freakin' code?

 

[syntaktik]

The only code that I am using is the one I posted, but, I will post my database here:
http://dl.dropbox.com/u/1254531/Inventory.accdb
I hope this would clarify my problem a bit more. Just try sorting the size colmn and you will see what I mean.

p.s. This is an inventory for someone else...