Ascending order Problem with field

[pablavo]

Hi

I have a report with many headers with sorting and grouping. All groups are Acsending properly apart from one which is a field with project years. The numbers of years in each group will go as high 14.

Even with Ascending order in "Sorting and Grouping" it goes like this:

1, 10, 11, 12, 13, 14, 2, 3, 4 etc. Instead of: 1, 2, 3, 4, 5, 6 etc.

Does anyone if there's a way to correct this? or could this be an access fault.

The Fields table properites datatype is set to "text" because sometimes there could be "1E". Could this be the problem?

 

[neileg]

Yes, it's because it is text. The sort order is correct for text fields. If the field is essentially numeric, what does 1E represent?

 

[pablavo]

Numerical And text 1E...

Thanks for replying Neileg.

The "E" stands for emergency. there are other Letters as well; "D"-development. "A" advocacy. Unfortunately I can't change this system so that it's just numerical.

Do you know if this mean that there's nothing I can do about this?

 

[pablavo]

I've just been looking at some articles and can't see any solutions to this one. Using The Val function within the Tables "advanced sort & filter" sorts as if it's numeric but misses out the all of the text part. (any text either is missed or becomes 0)

And the only real solution I can find for this one is to use padding, so 0's lead the numbers i.e. 001, 002, 003 rather than 1, 2, 3 Or change the field data type to number or Currency which I can't do.

ah well...

 

[neileg]

If you could split the field so you have just numeric and an optional letter, you could have a sort on both fields. Set up a query with two calculated fields, one to hold the number one for the letter. You could use IsNumeric() to test the last character in the field. If it's numeric, use Cint() to convert to number, if it's not, then use Right() to chop off the letter and Val()to extract the number.
Num: IIf(IsNumeric(Right([CodeNo],1)),CInt([CodeNo]),Val([CodeNo]))
Lett: IIf(IsNumeric(Right([CodeNo],1)),"",Right([CodeNo],1))