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

G

Guest

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
 
M

Marshall Barton

jf said:
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)
 
G

Guest

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

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.
 
M

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?
 
G

Guest

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

(david)

Marshall Barton said:
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.
 
M

Marshall Barton

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top