Report Data Grouping - Incorrect Order

[andy1210]

Could anybody help with this problem that I have been given by one of my colleagues. We have a report that groups contacts together depending on the code that we have given them, the problem is that they are not ordered correctly. They are ordered as 1,10,12,13....,2,20,21 and so on, where as they should be order 1,2,3,4,5,6....... The code is set as text, and can not be changed, I'm starting to think that the problem cannot be solved unless the code is set a number instead of text due to the way that access sorts them.
Thanks in advance for any help.

Andy

 

[MStef]

It means that you have to change "Data Type" from TEXT to NUMBER.

 

[andy1210]

Yeah I was beginning to think that as the code is set as text then it will always be sorted this way. I just wondered if there was any other setting that could be changed or if there was another way round it. At least I can tell my colleague that it can not be done without making changes to the database.
Thanks for your help.

Andy

 

[Fear Naught]

You could try adding code to convert the string to an integer using the CInt function and sorting on the result of that

I guess to do that the query behind report would need a column constructed something like this

Contact:CInt([contactID])

You would then group the report on that calculated column which would be numeric and give your correct grouping.

This assumes that the column in the database is called contactID of course

HTH. Let us know how you get on

 

[Rich]

=Val([YourField]) should do what you want

 

[andy1210]

Thanks for all the help, I have managed to solve the problem using the following function that Rich had also suggested -:

Expr1:Val([FieldNameGoesHere])

Thanks once again.

Andy