Sorting on a field with numbers

[Noel]

Hi. I have a report which I have set to group on a field
which contains a number - 1, 2, 3 etc. When I tell the
report to sort on this field I get the groups coming out
as 1,10,11,12,13,14,15,16,17,18,19,2,20,21,etc. I have
tried changing the Group Interval to 2 or 3 but no luck.
How can I get access to sort in the usual order,
1,2,3,4,5 etc? Thanks, Noel

 

[Cheryl Fischer]

Noel,

I suspect that the DataType of the field which contains your numbers is
actually Text. If that is so, then the contents of your field are sorting
correctly since Access (as well as most other DBMSs) sort text fields
character by character rather than by the value represented, which is why
you are seeing all of the records where the field begins with 1 together,
then all the records where the field begins with 2, etc.

The easiest way to fix this is to use a query as the record source for your
report. In that query you can add a Calculated Field, something like:

NumericGroup: CLng([Group Interval])

Then, in your report, you can sort on the field NumericGroup.

hth,

 

[Noel]

Thanks for the reply Cheryl and soory for not getting back
to you before now. You are correct - the field type is
(was) text. Cheers, Noel

-----Original Message-----
Noel,

I suspect that the DataType of the field which contains your numbers is
actually Text. If that is so, then the contents of your field are sorting
correctly since Access (as well as most other DBMSs) sort text fields
character by character rather than by the value represented, which is why
you are seeing all of the records where the field begins with 1 together,
then all the records where the field begins with 2, etc.

The easiest way to fix this is to use a query as the record source for your
report. In that query you can add a Calculated Field, something like:

NumericGroup: CLng([Group Interval])

Then, in your report, you can sort on the field NumericGroup.

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Hi. I have a report which I have set to group on a field
which contains a number - 1, 2, 3 etc. When I tell the
report to sort on this field I get the groups coming out
as 1,10,11,12,13,14,15,16,17,18,19,2,20,21,etc. I have
tried changing the Group Interval to 2 or 3 but no luck.
How can I get access to sort in the usual order,
1,2,3,4,5 etc? Thanks, Noel

.