sorting date field

[Angela Byars]

I have created a report that needs to have four fields sorted, one of them
being a date field. If the date field is first in the sort list it sorts
fine but the other three (two digit numeric fields) do not. If I move the
date field to fourth in the sort list, the first three numeric fields sort
fine but not the date field. I'm using Access97. Any ideas?

 

[Larry Linson]

What data type are the "two-digit numeric" fields? If those are character,
the text/character sort may not be what you'd expect from a numeric sort.

Where have you specified sorting -- in the query that is the Report's
RecordSource or in the Report's Sorting and
Grouping properties (the latter is the only reliable sort for a Report).

Do you also have Grouping specified? That, of course, will be the first
Sort... other sorting will only be inside the group(s).

Larry Linson
Microsoft Access MVP

 

[Angela Byars]

Larry,
The three 2-character fields are numbers but the only way I could keep the
leading zeroes was to change the data type to text. I have tried it both
ways, though (as text fields and as numeric fields).
I am using the Sort/Group function within the report properties but have
also tried specifying it in the query. I do not have Grouping specified.
As I mentioned in my original post, if the three
2-character fields are in the beginning of the sort it does sort them
properly but does not sort the date field at the end of the sort. Likewise,
if the date field is the first in the sort, it sorts the date properly but
not the rest of the fields in the sort.
Thank you for your help - Angela

 

[Angela Byars]

I figured out the problem.

Larry,
The three 2-character fields are numbers but the only way I could keep the
leading zeroes was to change the data type to text. I have tried it both
ways, though (as text fields and as numeric fields).
I am using the Sort/Group function within the report properties but have
also tried specifying it in the query. I do not have Grouping specified.
As I mentioned in my original post, if the three
2-character fields are in the beginning of the sort it does sort them
properly but does not sort the date field at the end of the sort. Likewise,
if the date field is the first in the sort, it sorts the date properly but
not the rest of the fields in the sort.
Thank you for your help - Angela

 

[Larry Linson]

Share the solution with us?

I figured out the problem.