Sorting on the value of a text box in each record?

[Kevin C Niven]

I'm trying to sort a report on the value of a text box. The value of
the txt box is built up of a number of IIf statements that together
create a string from a number of fields from my query. The string
ends up looking like:

Field1:Field2:Field3:Field4:Field5

But, if Field2, say, is null, then the string looks like:

Field1:Field3:Field4:Field5

and so on...

So, the value doesn't come directly from the query, but it is built up
from fields in the query.

My problem is I want the report to sort alphabetically on this created
value.

The way I've done this is by grouping on the expression:

=[Reports]![rptMyReport]![txtMyTextBox]

And sorting ascending.

But my report is not sorting correctly (more or less randomly). I'm
not sure why.

Can anyone suggest anything?

By the way, if I try simply grouping by Field1, then Field2, then
Field3, etc. I get, first, all the records that have four of the
fields null, then all that have three null, two null, and so on...,
which is not what I want. I want each character in the string to be
the next considered in the sort:

So I want it to sort like:

a:a
a:b:c:d:e
a:c:d


Many thanks,
Kevin

 

[KARL DEWEY]

Are you using the reports Grouping and Sorting function on you querys
calculated field?

 

[Duane Hookom]

The value for sorting and grouping must come from the report's record source.
You can't reference controls on the report.