Access Report, how to get numerical sort to sort 1,2,...not 1,10.

[jf]

In using an Access Report, set up to display Subdivision Plats numbered
1-458, we need the numerical sort to recognize numbers and sort in numerical
order i.e. 1,2,3,4....not 1, 10, 101, 2, 21, 200. Is there a simple answer
to this. I have used the option "sorting and grouping" it doesn't take care
of the problem. It seems like there should be an easy solution? Using
Access 2002. Thanks

 

[Marshall Barton]

In using an Access Report, set up to display Subdivision Plats numbered
1-458, we need the numerical sort to recognize numbers and sort in numerical
order i.e. 1,2,3,4....not 1, 10, 101, 2, 21, 200. Is there a simple answer
to this. I have used the option "sorting and grouping" it doesn't take care
of the problem. It seems like there should be an easy solution? Using
Access 2002.

Set the Sorting and Grouping to use an expression like:

=CInt(platnumberfield)

 

[jf]

Thanks Marshall for the quick reply..
the error message said: "Data type mismatch in criteria expression". The
data type is Text.

 

[Van T. Dinh]

Try creating a Calculated Field in the Query / SQL being used as the
RecordSource for the Report like:

NumVal: IIf(IsNumeric([SubDivision]), CInt([SubDivision]), 0)

(Change the zero to anything > 458 if you want to sort those that cannot be
converted to numeric.)

In your Report, group / sort by NumVal rather than SubDivision.

 

[Marshall Barton]

What criteria? How does a criteria expression come into
play?

The expression I was suggesting was supposed to be in the
report's Sorting and Grouping window (View menu), where
report sorting should be specified. Did you do something
else or does the platnumberfield contain more than just a
number?

 

[Guest]

Marshall, that's the error message I get when there
is a type error in the Sorting and Grouping.

(david)

What criteria? How does a criteria expression come into
play?

The expression I was suggesting was supposed to be in the
report's Sorting and Grouping window (View menu), where
report sorting should be specified. Did you do something
else or does the platnumberfield contain more than just a
number?
--
Marsh
MVP [MS Access]

the error message said: "Data type mismatch in criteria expression". The
data type is Text.

 

[Marshall Barton]

Thanks for letting me know David. I guess either I've never
made that mistake or my memory is fading fast ;-)

 

[Marshall Barton]

Now that David has straigtened me out, I should ask what you
entered in Sorting and Grouping, the name and field type of
the platnumberfield along with some samples field values
that fail.

The should be the same expression (without the = sign) that
you would use in the query's calculated field as Van
suggested.

If it's possible that the platnumberfield might be Null in
some record, then use Val instead of CInt. Val can
interpret some funny looking things as numbers (e.g. 1D-2)
so I try to avoid it when I can. OTOH, CInt can not deal
with Null values.