Correct sorting

[tmyers]

Is there a way to have Access not sort using lexicographic sorting?
It is confusing to see 1, 11, 12, 13, 2, 21, 3 etc. The field in question can contain letters, so it isn't a purely numerical field.
I see this way of sorting as very confusing to users.

 

[Minty]

Only by adding a sort order or some other value to sort on.

Remember that ancient Egyptian saying, Access can only do something you tell it to do.
If you want numbers sorted in text fields you have to add leading zero's

00001
00011
00111
00200

 

[Gasman]

I do not believe so.
You would need to create a numeric equivalent and sort by that.

FWIW I have seen 'professional' systems do the same and also with websites.

 

[Gasman]

Only by adding a sort order or some other value to sort on.

Remember that ancient Egyptian saying, Access can only do something you tell it to do.
If you want numbers sorted in text fields you have to add leading zero's

00001
00011
00111
00200

I thought that was @The_Doc_Man's saying ?

 

[tmyers]

Only by adding a sort order or some other value to sort on.

Remember that ancient Egyptian saying, Access can only do something you tell it to do.
If you want numbers sorted in text fields you have to add leading zero's

00001
00011
00111
00200

I thought about the possibility of leading zeroes, but I don't think that would work in this particular instance since the fields will almost always contain at least a single letter.

Case and point, the set I am trying to work with right now is LT4, LT5, LT11, LT12 etc, so the 11 and 12 are being sorted in front of the 3 and 5.

 

[MajP]

If that is your format you can do this in a query by returning the numeric portion and then converting to a numeric. Then sort on the numeric portion. I can get more complicated if you have other formats
LT1, LT12, LT12A, 1LT1A etc.

 

[Gasman]

So if they are always LTnn then you would only store the nn or concatenate LT,PR etc and nn as needed?

 

[tmyers]

So if they are always LTnn then you would only store the nn or concatenate LT,PR etc and nn as needed?

Unfortunately, that is not the case. The letters are constantly changing from job to job. Next job might just be L1, L2, L10 etc. The next might be XFT4, XGTZ6 ZYL9. Not a lot of rhyme or reason to them really (darn architect/engineers).

 

[tmyers]

If that is your format you can do this in a query by returning the numeric portion and then converting to a numeric. Then sort on the numeric portion. I can get more complicated if you have other formats
LT1, LT12, LT12A, 1LT1A etc.

Could that be done with the randomness of the letters like the example in my previous post?

 

[Gasman]

Unfortunately, that is not the case. The letters are constantly changing from job to job. Next job might just be L1, L2, L10 etc. The next might be XFT4, XGTZ6 ZYL9. Not a lot of rhyme or reason to them really (darn architect/engineers).

So far you only have text then a numeric.?
So you could split them as JobPrefix and JobNum and concatenate them when needed.

As the experts here say time and time again, it is easier to concatenate data as required than to constantly trying to split it as required.

 

[tmyers]

So far you only have text then a numeric.?
So you could split them as JobPrefix and JobNum and concatenate them when needed.

As the experts here say time and time again, it is easier to concatenate data as required than to constantly trying to split it as required.

In this particular instance I don't know how I would do it. The lettering and numbering are entirely nonsensical from job to job (these are essentially product designations). I am unsure how I could let the user input say A31, but store them as A and 31. It would ALWAYS have to displayed as A31 though.

I would have to have something that could do it regardless of the amount of letters, since that changes a well. One job might use one letter. The next may use 2 then the next might use 4. I have seen a job use 4 letters, a number, then ANOTHER letter (like RTLN54A).

 

[Minty]

I'm going to ask a potentially stupid question here, if as you stated

The lettering and numbering are entirely nonsensical from job to job...

I'm unsure what possible purpose there could be in sorting them?
Are you pointing out something that you find annoying, but that the end-user won't even notice?

 

[tmyers]

I'm going to ask a potentially stupid question here, if as you stated

I'm unsure what possible purpose there could be in sorting them?
Are you pointing out something that you find annoying, but that the end-user won't even notice?

Kinda sorta.
Sorting in this app is something I will never fully reconcile as the way it technically should be sorted, at times makes zero sense. R's before E's and such. This was just one of those things I wasn't sure if it could be easily done within a specific context. Mostly I have been handling it via grouping and using categories. I have also tinkered with the idea of sorting via the products autonumber, as the user will input them in the same way they appear on the construction schedule. Not quite fool proof though.

 

[The_Doc_Man]

The obvious solution is "don't sort by that field." If it can be close to random anyway, how would you sort randomly?

The only answer I ever found for such a thing is that you would create a "show it in this order" field, a sorting key OTHER than the field in question. There is no rule that an ORDER BY has to name a field that was in the list of SELECTed fields.

Not quite fool proof though.

Unless you tend to hire a lot of fools, perhaps you are safe?

 

[Gasman]

Sure each part must mean something?
So you have a prefix and number and an appendix.?
Is the appendix a version number, or something similar

It is not too much more work to enter

TGHJ - Tab - 1234 - Tab A

Just needs some training.?, if this is so important to you.?

 

[tmyers]

Sure each part must mean something?
So you have a prefix and number and an appendix.?
Is the appendix a version number, or something similar

It is not too much more work to enter

TGHJ - Tab - 1234 - Tab A

Just needs some training.?, if this is so important to you.?

Nope. They don't mean anything. Just a designation that an engineer assigned to them. I am just going to leave it be for now, and if users complain about it, may create another field to allow them to sort by.

 

[MajP]

What you need is a real easy way to apply a manual sort to a large list. Using the class module you can quickly turn a listbox into a sortable listbox. You can sort very large lists quickly and then persist the sort order. You can move items with buttons, double click, and for small lists drag and drop.

 

[Gasman]

Nope. They don't mean anything. Just a designation that an engineer assigned to them. I am just going to leave it be for now, and if users complain about it, may create another field to allow them to sort by.

Doesn't sound like an engineer to me?

 

[Pat Hartman]

I have a pretty complicated routine to parse drawing numbers so I can "sort" them. Engineers seem to like complex naming formats. If you can determine a pattern, we can help with the code

 

[Galaxiom]

BTW When ordering by numbers stored as text field:

ORDER BY CInt([textfield])

I haven't tested it but I expect this would be more efficient than something like:

 ORDER BY Format([textfield],"00000")