unusual SORT order required

[mark kubicki]

I have a form where the data needs to be sorted in a somewhat logical, but
illogical order (see below for the sequence)...

without (unless I have to) a forever long formula.. is there and easier way
to get there?
many thanks in advance, mark

TA, TB, TC, TC-1, TD, TD-EM, TE, TE-1, TE-2, TE-EM, TF, TG...TZ, TAA, TAB,
TAC...TAZ, TBA, TBB, TBC
(if the sequence is not obvious... just let me know: i'll flow chart it for
you)

 

[fredg]

I have a form where the data needs to be sorted in a somewhat logical, but
illogical order (see below for the sequence)...

without (unless I have to) a forever long formula.. is there and easier way
to get there?
many thanks in advance, mark

TA, TB, TC, TC-1, TD, TD-EM, TE, TE-1, TE-2, TE-EM, TF, TG...TZ, TAA, TAB,
TAC...TAZ, TBA, TBB, TBC
(if the sequence is not obvious... just let me know: i'll flow chart it for
you)

Create a table.
Enter each value in one field, and it's wanted sort order in a Number
field, i.e.
SortName(test) SortOrder (Integer)
TA 1
TB 2
TC 3
TC-1 4
etc....

(Note: the SortNames and SortOrder values do not need to be in any
particular order in the table.)
Then include this table in the query that is the form's or report's
record source. Make sure there is a relationship between the SortName
field and the actual corresponding field in the table.
For a form, order the query on this SortOrder field.

For a report, order the query on the SortOrder field in the Report's
Sorting and Grouping dialog.

Changing the sort order in the future is done simply by changing the
SortOrder values in the table.

 

[Marshall Barton]

I have a form where the data needs to be sorted in a somewhat logical, but
illogical order (see below for the sequence)...

without (unless I have to) a forever long formula.. is there and easier way
to get there?
many thanks in advance, mark

TA, TB, TC, TC-1, TD, TD-EM, TE, TE-1, TE-2, TE-EM, TF, TG...TZ, TAA, TAB,
TAC...TAZ, TBA, TBB, TBC
(if the sequence is not obvious... just let me know: i'll flow chart it for
you)

Normally, you should deal with odd sort orders by using a
field in a (new?) table that explicitly specifies how the
values should be sorted. E.g. a new table with two fields:
SortVal CatName
1 TA
2 TB
3 TC
3.3 TC-1
4 TD
4.5 TD-EM
etc.

Then join that table in form's record source query.

In this case (as long as these thing will always follow the
same pattern), you might be able to get away with uaing a
quick and dirty approach:

ORDER BY

IIf(InStr(field,"-")>0,InStr(field,"-")-1,Len(field),
field

 

[mark kubicki]

thanks everyone...

the one detail that I did not mention is that although I listed a sort order
of: TA, TB, TC, TC-1, TD the actual project records might be:

TA, TA-1, TA-2, TA-3, TA-4..., TC, TC-27, TD, TE, TF... (and other
variations of ...)

let me know if you might want to re-assess to approach

-mark
----------------------------------------------------------------

 

[Douglas J. Steele]

Try putting a couple of computed fields in your query, and sort on them.

Sort1: IIf(InStr([Field], "-") = 0, [Field], Left([Field], InStr([Field],
"-") - 1)
Sort2: IIf(InStr([Field], "-") = 0, 0, Val(Mid([Field], InStr([Field], "-")
+ 1))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

thanks everyone...

the one detail that I did not mention is that although I listed a sort
order of: TA, TB, TC, TC-1, TD the actual project records might be:

TA, TA-1, TA-2, TA-3, TA-4..., TC, TC-27, TD, TE, TF... (and other
variations of ...)

let me know if you might want to re-assess to approach

-mark
----------------------------------------------------------------