sorting table field in ascending order

[picyx]

I have a table with 2 fields, the ID and a field called BODY. I want the field called BODY to be sorted in ascending order. The valueas I have in this field are B-2083, B-2281, B-969 B-900, B-817, B-2148. When I sort in ascending order I get B-2083, B-2148 B-2281, B-817, B-900, B-969. I want B-817, B-900, B-969, B-2083, B-2148, B-2281. I see why the sort comes out the way it does but is there a way to get it to come out the way I want?

Thank you
Paul

[ranman256]

strings do not sort like numbers, so you must make numbers in an extra field in the query.

NumVal:cint( mid([body],3))

then sort this field.

[thebigthing313]

Do all values in [BODY] start with "B-"? If so, you should just save the numbers in a Number field. Then you wouldn't need to worry about having to convert. Saves database space as well. Then on a form, query or report, if you want the "B-" then you'd just use the expression

="B-" & [BODY]

If you do, however, have different letters that precede the number in [BODY], then ranman256's solution becomes limited since something like A-2056 and B-2056 will be put next to each other. You'd also get errors if you have something like "AB-343".

[picyx]

Thank you all for the input. thebigthing313, where would I put your expression?

[thebigthing313]

If you choose to just store the numbers? You would put that wherever you needed. In a form or report, it'd be the control source of a field. In a query, it'd be a column.

[picyx]

thebigthing313, I like the idea of putting your string in a form. I have tried it in the control source property but it does not work. Nothing changes. I took the data from the table and put it in a query, sorting the column. I then reference that query in the form and put your string where I said but nothing changes, the list just contains the sorted numbers. Your help is really appreciated.
Thank you

[thebigthing313]

You mentioned list, are you using a combobox or list box?

If so, then follow these steps:

Select the control, then edit it's RowSource property. This should bring you to the query builder. In the query builder, add a column with the calculation:

Expr1: "B-" & [BODY]

Depending on how many number of columns you have in the query, adjust your control's Column Count and Column Widths properties.
For example, if your query returns something like this

2083 // B-2083
2991 // B-2991

Your Column Count is 2
Your Column Widths is 0;1"

Basically, you have two columns, but you are hiding the first one (0 width).

[June7]

If you have the numeric part in a separate field (either native to table or calculated in query) then sort by that field. Have another field with B- prefix string for display on report.

If you want to sort by string with number sequence, then the number part would need placeholder zeros:

B-0817, B-0900, B-0969, B-2083, B-2148, B-2281

These placeholder zeros can be calculated in expression that concatenates text with number:

"B-" & Format([BODY], "0000")

Since you already have the data saved with the B- prefix, need to extract the number part and use that constructed field for sort criteria.