order by on reports specfic sort order

[jebushatescats]

Hello all

I need to be able to sort my report by an incoming field called entity_code, this I know how to do but...
the entity_code field will have one of four values
sess
crse
prov
part

If I just order by this field the records will display on the report in the following sort order
crse
part
prov
sess

What I want is to be able to have them sort by the following order

prov
crse
sess
part


Is there a way to hard code in a sort order based on the value of the entity_code?

 

[Remou]

You can do this with a query. Create a table with the sort order and the four values. You can then join this table by Entity_Code to your main table, allowing you to select the SortOrder field to sort by in your report.

 

[dhookom]

I would use Remou's solution but there is a short cut if your values will never ever change. Set the sorting and grouping expression to:
=Instr("prov crse sess part",[entity_code])
It is much more functional though to maintain data in applications rather than maintaining expressions.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jebushatescats]

thanks for the replys !!