Sorting Query

[Gavx]

I am trying to apply a sort to records that contain a description field that looks like this;

Item ID | Item Name
166 | All Mountain Lift Pass - 10-Day(12Days)
165 | All Mountain Lift Pass - 1-Day

The sort is applied to Item Name but ranks Item 166 before Item 165 whereas I need it the other way around. And no I cannot sort by the Item ID (autonumber) because these records are not necessarily created in alphabetical correct order.

How could I fix this?

 

[plog]

Either change the name so it sorts like you want, or break out the data after the hyphen into new fields and then sort by them, or add a new field to specify the sort order.

You can't treat parts of strings like text and other parts like numbers. My advice would be to spell out the numbers 10 & 1:


All Mou...One-Day
All Mou...Ten-Day(12Days)

However, if you also have 2, 3 or any other numeric day pass in the data that method won't work: eight, five, nine, one, seven, six, ten, three, two

 

[Gavx]

I think I will investigate a procedure that entails a form whose purpose is to maintain these items - assigning a sort order index on the fly. The challenge will be for example to after update of each record's sort number the list to be reshuffled in accordance with any sort numbers I might change....hmmm

 

[Brianwarnock]

Why will you change the sort number, won't you just allocate a number equal to the number of days?

Brian

 

[Gavx]

Good suggestion.
These products exist in a table called Products and includes items other than lift tickets. For example accommodation, transport options etc. The table has a product group number which keeps like products together. However there are products within the same product group which don't have days but need to appear within their subgroup in a particular order.

 

[Brianwarnock]

Without actually fully understanding the data it is impossible to give a detailed suggestion, also as I no longer have Access I could not trial anything, but I would be looking at giving those products without days a number well out of the reach of days, say 1000, and incrementing them by more than 1 so that I could infill

Something like 1010 1020 1030

Brian