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!

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

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!

Close:

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

???
kh

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... :confused:

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))

That worked like a charm! Thank you! :p

Sorry Jeo, I missed the second set of parens :o

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...

Sorry Jeo, I missed the second set of parens :o

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

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

kh

The PK, as with any lookup table ;)

I suppose that would work as well... ;)

kh