Report, group by expression with string functions

[larschris]

In a MS Access report I need to sort and group by 2 fields (using one
header) and break when any of them changes. The fields are an integer
(Int) followed by a string (Str). If I write [Int] & [Str] in the
Field/Expression column it works ok except for the sorting.

[Int] may have 1 to 3 digits and in order to sort correctly I need to
put 0, 1 or 2 blanks or zeros in front to add up to 3 characters. I try
to do this in the expression by writing [Right(Space(2) & [Int],3)] &
[Str] but I get a syntax error. It seems the functions are not accepted
in the expression.

I would like to avoid maintaining any extra field for this in the
database table.

Any suggestions to get the sorting right would be greatly appreciated.

Sincerely larschris

[Rog]

Add the following column to your underlying query:

SortOrder:Format([int],"000") & [str]

and use that to sort the report.