Solved Sorting Report by Text Field that has Numbers & Letters

[Weekleyba]

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.

 

[CJ_London]

you'll need a different field to base your sort on

sortorder: IIf([ProjectPriority] Is Null,1,IIf([ProjectPriority]="OBL",2,0))

If it is a number, then leave as the number.

It is not a good idea to mix text and numbers in the same field, so pretty sure your number will be treated as text and will sort accordingly (i.e. 11 will come before 2)

 

[theDBguy]

Hi. You could also try the following in the report sorting.

Nz([ProjectPriority],"NP")

If that doesn't work, we could add one more sorting layer.

 

[Gasman]

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.

Reports take NO notice of the way the data is sorted?

 

[Weekleyba]

Reports take NO notice of the way the data is sorted?

Unless you include in the report a Sort By 1expr in the report. Right?

 

[Weekleyba]

Right now I have it set up this way:

Running the report gives the following where the Project Priority is the second column from the left:

As you can see, the letters "OBL" are coming before the numbers 1,2,3, etc.
Any ideas how to change that easily?

 

[CJ_London]

not sure if your read my post - but that is nothing like what I suggested

edit - and you don't need to sorts in the query - they are ignored by the report

 

[Weekleyba]

Thanks guys for your great help.
I managed to get the outcome I need by using your suggestions.

I added to the query that feeds the report, the following two expressions:
1. ProjectPriority1: Nz([ProjectPriority],"NP")
2. sortorder: IIf([ProjectPriority] Is Null,1000,IIf([ProjectPriority]="OBL",2000,Val([ProjectPriority])))

I then set the field in the report to ProjectPriority1 and added a Sort By sortorder.

End result looks good.
Thanks again CJ and DBguy!

 

[CJ_London]

that will work until your projects exceed 999

good luck

 

[theDBguy]

Thanks guys for your great help.
I managed to get the outcome I need by using your suggestions.

I added to the query that feeds the report, the following two expressions:
1. ProjectPriority1: Nz([ProjectPriority],"NP")
2. sortorder: IIf([ProjectPriority] Is Null,1000,IIf([ProjectPriority]="OBL",2000,Val([ProjectPriority])))

I then set the field in the report to ProjectPriority1 and added a Sort By sortorder.

End result looks good.
Thanks again CJ and DBguy!

Glad to hear you got it sorted out. Good luck with your project.