Sorting by ignoring certain letters

[Guest]

I'm writing a report in Access 2003 and I am having an issue sorting it.

I want to sort the column alphabetically by color. The problem is that often
times the color is preceded by an EC, PC or EC/PC and sometimes is proceded
by nothing at all. Is there a way to sort that would ignore these three
combinations and sort by the color that imediately follows? I'm not good at
writing formulas yet and I think that's mainly why I'm having trouble. Thanks
a lot.

 

[Allen Browne]

The best solution would be to create a separate field to hold the color
prefix. It could be EC, PC, the combination, or blank.

This approach meets the design goal of storing only one thing in a field
(the data is atomic), and solves the sorting issue.

 

[Guest]

I agree with you, unfortunatly I'm stuck with the Table I've been given. I'd
settle for the second best solution if you have one. )

 

[Douglas J Steele]

Add a computed field to your query that eliminates the appropriate prefixes,
and sort on that field.

Something like the following should work:

IIf(Left([MyField], 2) = "PC", Mid([MyField], 3), IIf(Left([MyField], 5) =
"EC/PC", Mid([MyField], 6), IIf(Left([MyField], 2) = "EC", Mid([MyField],
3), [MyField])))

 

[Allen Browne]

2nd best solution is to create another field to contain the values exactly
as they should be sorted (i.e. without the prefix). This is the typical
solution where you need the flexibilty to sort a title such as:
Hardware Cookbook, The

The most inefficient solution would be to locate the space between words
(assuming that no actual colour names consist of 2 words), using Instr(),
and Mid(), or possibly using Replace() to lose the specific prefixes. The
result then goes into the ORDER BY clause of your query.