Sort text field in report

[dje]

I have a text field that contains either the word "cash" or a number from 1
upwards. I have made it a text field due to the cash option.

I have a report that is sorted by this field but I am getting the result:

Cash
1
10
11
2
3 etc

Rather than the desired
Cash
1
2
3...
9
10
11

Is there any way the "text" numbers can be sorted in the right (i.e.
numeric) order. It doesnt matter whether Cash is at the top or bottom but has
to be included.

All suggestions gratefully received.

 

[Allen Browne]

Create a query.

Type an expression like this in the Field row:
Amt: Val(Nz([Amount],"0"))

Use the query as the Record Source for the report.

Sort the report by this field.

(Of course, it would be *much* more efficient to use a Number of Currency
field. You can display cash for null in the report if you need to.)

 

[dje]

Thanks for your assistance.

Create a query.

Type an expression like this in the Field row:
Amt: Val(Nz([Amount],"0"))

Use the query as the Record Source for the report.

Sort the report by this field.

(Of course, it would be *much* more efficient to use a Number of Currency
field. You can display cash for null in the report if you need to.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a text field that contains either the word "cash" or a number from 1
upwards. I have made it a text field due to the cash option.

I have a report that is sorted by this field but I am getting the result:

Cash
1
10
11
2
3 etc

Rather than the desired
Cash
1
2
3...
9
10
11

Is there any way the "text" numbers can be sorted in the right (i.e.
numeric) order. It doesnt matter whether Cash is at the top or bottom but
has
to be included.

All suggestions gratefully received.