Question Sort order

[staylor]

I'm sure this has been asked several hundred times, but how do I get items to sort like this : uw-10, uw-100, uw-200, uw-1000, instead of the typical uw-10, uw-100, uw-1000, uw-200 order. i imagin it has something to do with sorting by character count first then alphanumeric value.

 

[georgedwilkinson]

Well, if your data is in separate fields, you can just order by the prefix and then by the body. I'm guessing that's not the case, though.

Not sure where you're trying to sort it, but in a query (if your format is consistent), you could do something like this:

order by left(MyField,Instr(MyField,'-') - 1), val(right(MyField,len(MyField) - Instr(MyField,'-')))

This is air code, I haven't tested it, I just got back from lunch and am not on my game...beware.

 

[staylor]

Lunch is a good thing.

I'm sorting the row source type in a quary for a combo box. But the only options I have for sorting are ascending and descending.

I'm very new to Access and databases.

 

[FoFa]

You need to go into SQL mode in your query the combobox is based off and replace your order by with what he had.
Basically it looks for the "-" and takes the stuff after it as numbers and sorts it numerically.

 

[georgedwilkinson]

Go to the SQL view of your query and paste in my code (with your field name) at the bottom of the query. Then go back to the design mode to see what it did. I think you'll be pleasantly surprised (crossing my fingers).

 

[georgedwilkinson]

Yeah, what the tall stranger from Texas said!

 

[staylor]

THANKS!!!!! That was the ticket. It still amazes me that they dont have basic functions for something like that.

Now on to the next problem. 8-/

 

[staylor]

you can lock it down.

btw...stickty that coding.

 

[Xoote]

Thanks for the info i was wondering the same thing

 

[georgedwilkinson]

It still amazes me that they dont have basic functions for something like that.

Actually, they do (I think). I'm just old school.

 

[FoFa]

If you were using VBA, you could use the SPLIT function to break the string apart, but the SPLIT function requires an array to store the values in.

 

[Brianwarnock]

THANKS!!!!! That was the ticket. It still amazes me that they dont have basic functions for something like that.

Now on to the next problem. 8-/

How would you specify this "basic" function?
How many types and occurences of each type of data will you allow for?
uw-100 is a character string and that is how the high speed pedantic moron called a computer will sort it, it is up to the user to get his data correct.

Brian

 

[FoFa]

The SPLIT function would store the data in an array. It is up to you to convert it when pulling it out, just like you did (BTW that has been how I have done it for many years, like you did). Functionally there is little difference, just a tad easier to read.
So
dim SomeArray
SomeArray = SPLIT([column],"delimiter")
The SomeArray would have the first occurance in the first index, second in the second, etc. The advantage here is you do not need to know how many delimited fields there are, you can check using UBOUND on the array. In the case of this example where it is uw-100, it would always be two. But sometimes when parsing some odd string or an entire record, that feature is nice. Also when you get over about 3 segments, it is much shorter.

 

[mikejarjoura]

Thank you so much for the sort order posting, it did the trick for me. I also have a switch (a button for reverse sort) that lets me sort in DESC descending order. Would you be able to share how I might be able to reverse the order of the sort?

Here's the VB line I ended up using...


strSortSQL = " ORDER BY left(tblShipInformation.strHullName,Instr(tblShipInformation.strHullName,' ') - 1), val(right(tblShipInformation.strHullName,len(tblShipInformation.strHullName) - Instr(tblShipInformation.strHullName,' ')))"


Let's say the data is as such...

ABC 1
ABC 9
ABC 15
ABC 652
ABC 1005

It works brilliantly in ascending order, just need it to be able to do a Descending sort. Thank you for any assistance.

-Mike