Sort text field in report


D

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

Advertisements

A

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

Advertisements

D

dje

Thanks for your assistance.

Allen Browne said:
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.

dje said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top