sorting

[cc]

I want a report to sort in ascending order by the number in a field. The
numbers are 1A, 1B, 2-9, 10, 11A, 11B, and 12-18.

It is sorting in this order - 10, 11A, 11B, 12, 13, 14, 15, 16, 17, 18, 1A,
1,B, 2-9.

I want it to sort 1A, 1B, 2-9, 10, 11A, 11B, 12-18.

How can I fix this?

 

[Jeff Boyce]

If your field has [optionally] both numbers and letters, you have a text
field, not a numeric field.

If your field needs to be sorted first by the numeric value, why don't you
have the numeric and the alphabetic values in separate fields?!

If you cannot use two fields to separate these two values, create a query.
Add the table, add the field. Now create a new field, something like:
NumberSort: Val([YourTextField])

Sort on that new field.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KARL DEWEY]

You could add a field just for sorting and use this type of number structure

 

[cc]

Thank you, Karl. I got three replies with good ideas. I am most
appreciative to all of you.

You could add a field just for sorting and use this type of number structure
---
01A, 01B, 02-9, 10, 11A, 11B, and 12-18.

IIf(Asc(Mid([YourField],2,1)) Not Between 48 And 57,"0" &
[YourField],[YourField])

--
KARL DEWEY
Build a little - Test a little

I want a report to sort in ascending order by the number in a field. The
numbers are 1A, 1B, 2-9, 10, 11A, 11B, and 12-18.

It is sorting in this order - 10, 11A, 11B, 12, 13, 14, 15, 16, 17, 18, 1A,
1,B, 2-9.

I want it to sort 1A, 1B, 2-9, 10, 11A, 11B, 12-18.

How can I fix this?

 

[cc]

Thank you, Marshall. I got three replies with good ideas. I am most
appreciative to all of you.

 

[cc]

Thanks, Jeff. I got 3 replies with good ideas. I am most appreciative to
all of you.

If your field has [optionally] both numbers and letters, you have a text
field, not a numeric field.

If your field needs to be sorted first by the numeric value, why don't you
have the numeric and the alphabetic values in separate fields?!

If you cannot use two fields to separate these two values, create a query.
Add the table, add the field. Now create a new field, something like:
NumberSort: Val([YourTextField])

Sort on that new field.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I want a report to sort in ascending order by the number in a field. The
numbers are 1A, 1B, 2-9, 10, 11A, 11B, and 12-18.

It is sorting in this order - 10, 11A, 11B, 12, 13, 14, 15, 16, 17, 18,
1A,
1,B, 2-9.

I want it to sort 1A, 1B, 2-9, 10, 11A, 11B, 12-18.

How can I fix this?

 

[Marshall Barton]

I want a report to sort in ascending order by the number in a field. The
numbers are 1A, 1B, 2-9, 10, 11A, 11B, and 12-18.

It is sorting in this order - 10, 11A, 11B, 12, 13, 14, 15, 16, 17, 18, 1A,
1,B, 2-9.

I want it to sort 1A, 1B, 2-9, 10, 11A, 11B, 12-18.

Set the report's Sorting and Grouping to use two levels:
=Val(thefield)
thefield