Sort on a Value List

[jeo]

I have a field called Criticality with these values: Low, Medium, High.
On my report is there a way to sort it by these values, lets say from Low to High?
In the sort/group menu I can only sort in Ascending or Descending.
Thanks!

 

[KenHigg]

Use a query as the underlying recordsource for the report adding a sort field and use an series of iif()'s to return 1, 2 or 3 for low, med and high. Then use this fld to sort the report records.

???
kh

 

[jeo]

I'm guessing it would be something like this, right?
Criticality: (IIf[Criticality]="Low", 1), (IIf [Criticality]="Medium", 2), (IIf [Criticality]="High", 3)

This is not working...returning an error.
Not really sure if what I'm doing is correct.
Thank you!

 

[KenHigg]

Close:

CriticalityNumeric: IIf([Criticality]="Low", 1, IIf [Criticality]="Medium", 2, 3)

???
kh

 

[jeo]

It's saying:"You must enclose IIf function arguments in parentheses."
I'm adding this:
CriticalityNumeric: IIf([Criticality]="Low", 1, IIf [Criticality]="Medium", 2, 3)
as a new field in my query, right?
I must be missing something...sorry...

 

[Rich]

If you'd used a look up table with the corresponding values 123 etc you could have done it without a calculated field, other than that the statement should be

IIf([Criticality]="Low", 1, IIf ([Criticality]="Medium", 2, 3))

 

[jeo]

That worked like a charm! Thank you!

 

[KenHigg]

Sorry Jeo, I missed the second set of parens

kh

If you'd used a look up table with the corresponding values 123 etc...

But then if you need the textual representation you'd still be hosed - go figure...

 

[Rich]

Sorry Jeo, I missed the second set of parens

kh




But then if you need the textual representation you'd still be hosed - go figure...

No you wouldn't, the look up table would have two columns, the numeric field and the textual value, you'd simply hide the numeric field on the Report

 

[KenHigg]

What value would you store, 1, 2, 3, or Low, Med, High?

kh

 

[Rich]

The PK, as with any lookup table

 

[KenHigg]

I suppose that would work as well...

kh