Order by Text box on Report

[DMT Dave]

Hi Guy's hope you are all safe and well, this should be my last challenge before Christmas and all the best to all....



I have a field on a report in which I have split the field into 2, one is called SoNumber (field from table) in the SO Number i will have something like:

SL-123456 (always 6 digits after SL-)

I have copied the text box and placed it to the right of it in the detail section of the report and done the following:

Left hand text box now has control source of: =Left([SONumber],6)

Right hand text box has a control source = = Mid([SONumber,7)

I now want to be able to sort by the right text box if this is possible ? as it is not listed in the sort by options

So the full number could be SL-248952 and next Record could be SL-249018, now the one that starts in 249 is actually a lower number because of the last 3 digits 018 and 952

Hope i have made sense and not mashed your brains

Kindest

[davegri]

In order to get those calculated fields in the sort options, they'll have to be included in the report recordsource.

[ranman256]

yes, put them in the query. map the text boxes to those new fields.
add the sort field to the report.

[DMT Dave]

Okay guys thank you, i think may add a field to the backend called something light SLRight the run an update query using the Mid option etc...

Its sounds like the way to go ?

Kindest and stay safe

[davegri]

No, no. Don't store calculated fields in the table. Apparently you have the table set as the recordsource for the report. Make a query based on the table and add the two calculated fields to the query, then use the query as the report recordsource. That's what ranman is also saying.