possible Sorting with If Then?

[TTN]

I need to be able to sort by two methods. I'll try to explain only my isolated problem.

I have one type field set to be either A-E with multiple values possible. The other field is a title.

I want to sort by type A,B,C,D, or E then by alphabetical title name. However, if the record type contains a C, I want it to sort by title only, not by type. The reason is because I have a series of information that needs to be shown in a sequential order. An example follows. Any suggestions?

Senators of the Nation - Type A

Typecasting - Type A

Ah Quon McErath, A Hero's Story - Type A

Unit 1 - Type C,D

Unit 2 - Type C,F

Unit 3 - Type C,E

Unit 4 - Type C,D

Unit 5 - Type C,F

An American Tradition - Type A

Lowering the Barriers - Type A

Unit 6 - Type C,F

Unit 7 - Type C,E

So, it sorts out as:

Ah Quon McErath, A Hero's Story - Type A

An American Tradition - Type A

Lowering the Barriers - Type A

Senators of the Nation- Type A

Typecasting - Type A

Unit 1 - Type C,D

Unit 4 - Type C,D

Unit 3 - Type C,E

Unit 7 - Type C,E

Unit 2 - Type C,F

Unit 5 - Type C,F

Unit 6 - Type C,F

[-Queue-]

Well, I would say create a calculated text field of Case( not Position( Type, "C", 0, 1 ), Type & " " ) & Title, and sort by this field. But your example doesn't seem to demonstrate your desire. Why does Unit 4 sort before Unit 3, etc.?

[TTN]

Thanks. I want it to sort as:

Ah Quon McErath, A Hero's Story - Type A

An American Tradition - Type A

Lowering the Barriers - Type A

Senators of the Nation- Type A

Typecasting - Type A

Unit 1 - Type C,D

Unit 2 - Type C,F

Unit 3 - Type C,E

Unit 4 - Type C,D

Unit 5 - Type C,F

Unit 6 - Type C,F

Unit 7 - Type C,E

But it's sorting as my example in my initial post instead. Right now, the sort function is simply Sort by Type, ascending, then Sort by Title, ascending.

Will your calculation allow me to sort by alpha if anything other than "C" is contained in the type field?

[-Queue-]

Yes, If Type contains the letter C, then it won't appear in the calc field, but the Title will. If it doesn't contain C, then both will appear.

[TTN]

Thank you. Thank you! This worked beautifully. Now I need to work on understanding why it worked! Much mahalo.

[-Queue-]

Position( Type, "C", 0, 1 ) returns either zero or the position of C in Type. If it's zero, then 'not Position( Type, "C", 0, 1 )' is true, since 'not X' is equivalent to 'X = 0'. So if the result is zero and, therefore, not Position( Type, "C", 0, 1 ) is true, the calculation will return Type, followed by a space. In either case it will return the Title, but only if C is not present in Type will it return the Type and a space.