I have a report that I want sorted by a text field called [ProjectPriority].
The field can be null, a number or text.
If it is null, I want the field to read "NP" for No Priority.
If it is "OBL" then read "OBL". The field is set to "OBL" once a award date for the contract is entered. OBL stands for Obligated.
If it is a number, then leave as the number.
I want to sort this field by numbers is ascending order, then by letters.
In the query, I've tried and expression, 1expr: IIf([ProjectPriority] Is Null,"NP",IIf([ProjectPriority]="OBL","OBL",Val([ProjectPriority]))) which is close but it gives me the letters prior to the numbers.
How do I switch this around to have the numbers first then the letters. ie. 1,2,3,4,5....NP,NP,NP,OBL,OBL.OBL.....
Once I have the query corrected, I believe it will sort correctly in the report.....but not sure yet.
The field can be null, a number or text.
If it is null, I want the field to read "NP" for No Priority.
If it is "OBL" then read "OBL". The field is set to "OBL" once a award date for the contract is entered. OBL stands for Obligated.
If it is a number, then leave as the number.
I want to sort this field by numbers is ascending order, then by letters.
In the query, I've tried and expression, 1expr: IIf([ProjectPriority] Is Null,"NP",IIf([ProjectPriority]="OBL","OBL",Val([ProjectPriority]))) which is close but it gives me the letters prior to the numbers.
How do I switch this around to have the numbers first then the letters. ie. 1,2,3,4,5....NP,NP,NP,OBL,OBL.OBL.....
Once I have the query corrected, I believe it will sort correctly in the report.....but not sure yet.