Text Sequence Order

[LRay67]

I have a report that I need to sequence in the following order:

AIM-BR1
AIM-BR2
etc.

Currently I get the following:
AIM-BR1
AIM-BR11
AIM-BR12
etc.

Is there a way to correct this were it will read the correct order?

Thanks

 

[fredg]

I have a report that I need to sequence in the following order:

AIM-BR1
AIM-BR2
etc.

Currently I get the following:
AIM-BR1
AIM-BR11
AIM-BR12
etc.

Is there a way to correct this were it will read the correct order?

Thanks

It is sorting in the correct order (as text).
What you are wishing to do is sort it on the number value that starts
at the 7th position in the string.
So, assuming all of the preceding text is in the form of "AIM-BR" (or
any 6 character string) open the report's sorting and grouping dialog
(View + Sorting and Grouping).
In the Field/Expression column write:
=Val(Mid([FieldName],7))
Set the Sorting to Ascending.

 

[Matt Wickham]

Good solution if you always know the significant character is the 7th one.
But I suspect that is not always the case.

One other obvious solution I can think of is to create a related numberic
sort value field associated with every code that would be used to sort the
report, but not print (uncheck the Show attribute on the query for the
report).

Something like:

Code CodeSort
AIM-BR1 00001
AIM-BR2 00002
AIM-BR11 00011
AIM-BR12 00012
AIM-BR111 00111
BBB-BR1 01001

But this requires knowledge of the structure of all possible code values and
could break down over time, even if you got it to work now. As usual in
relational databases, this is really a design problem. You probably need to
break out your coded field into multiple fields, something like

Company AIM Branch Number 1 Company BBB branch number 1, etc..