Custom Sort for Control on a Subreport

[Guest]

I need to develop a custom sort order for a control called ContactType on a
subreport. I've read in posts that this must be defined on the report Sorting
and Grouping rather than in the underlying query. The expression I am using
is too long for the Sorting/Grouping dialog box. It contains 15 IIf
statements that are structured as follows:

IIf([ContactType]=“POCâ€,1,IIf([ContactType]=“POC Altâ€,2)), etc. etc. I also
tested it in the query also, but I get a wrong syntax error message.

Can anyone offer help on this question?

 

[Duane Hookom]

15 IIf()s should be 15 records in a table
ContactType SortVal
POC 1
POC Alt 2
SOL 3
PEBKAC 4
ROFLMAO 5
TTFN 6
ETC 7
You can then include this table in your report's recordsource and sort by
the SortVal column.

 

[Marshall Barton]

I need to develop a custom sort order for a control called ContactType on a
subreport. I've read in posts that this must be defined on the report Sorting
and Grouping rather than in the underlying query. The expression I am using
is too long for the Sorting/Grouping dialog box. It contains 15 IIf
statements that are structured as follows:

IIf([ContactType]=“POC”,1,IIf([ContactType]=“POC Alt”,2)), etc. etc. I also
tested it in the query also, but I get a wrong syntax error message.

The Switch function would make it easier to write this kind
of thing (see Help for details).

But it would be far more general to create a table that
provides the custom sort order values.

tblContactSort:
fldContactType Text
fldContactSort Integer

Then you can Join that table to your report's record source
table/query on the contact type field and make the sorting
value available to the report.

 

[Guest]

Thanks to both Duane and Marsh. I had created a separate table -- but then
didn't know what to do with it. After changing the value field to Number, and
attaching the table to my record source, it's working perfectly. Thank you
both so much!

I need to develop a custom sort order for a control called ContactType on a
subreport. I've read in posts that this must be defined on the report Sorting
and Grouping rather than in the underlying query. The expression I am using
is too long for the Sorting/Grouping dialog box. It contains 15 IIf
statements that are structured as follows:

IIf([ContactType]=“POCâ€,1,IIf([ContactType]=“POC Altâ€,2)), etc. etc. I also
tested it in the query also, but I get a wrong syntax error message.

The Switch function would make it easier to write this kind
of thing (see Help for details).

But it would be far more general to create a table that
provides the custom sort order values.

tblContactSort:
fldContactType Text
fldContactSort Integer

Then you can Join that table to your report's record source
table/query on the contact type field and make the sorting
value available to the report.