Simple Sort Not Working Correctly

[Mike555]

I have a query which sorts by a field named ReceivedDate. When I run the query the sort works fine. I have a report bound to this query as it's datasource, and the report also sorts and groups by ReceivedDate. However, sporadically throughout the report the dates are not sorted correctly.

An example of what the report shows...

ID# ReceivedDate
1 01/01/05
2 01/02/05
3 01/03/05
4 01/01/05
5 01/01/05
6 01/04/01
7 01/02/05

I have no module for this report, so there is no VBA interfering with this. Also I do not have the Report's Sort By property set. The sorting for the report is specified in the Sorting/Grouping properties window.

Any ideas how I can troubleshoot and/or determine the problem here? Thanks.



--
Not until I became a Network Administrator did the error message "See your Network Administrator for assistance" become petrifying.

 

[THWatson]

From your example, it appears that the first sorting applied is the ID#.

What are your sorting and grouping values?

Tom

 

[Tranman]

Mike,
Hard to know, but are you *sure* there is no other sorting/grouping going on besides just the date? Like maybe grouping by customer # or something like that?

As far as troubleshooting, I'd (going by your example), just look at the 01/01/05, the 01/02/05, and the next 01/01/05. There just about has to be some commonality between the first 01/01, and the 01/02, which does not exist between the first 01/01 and the second 01/01.

Also, remember that there is more to date/time fields than meets the eye. If the field is getting populated by something like the Now() function, it will also have the time as part of the field. That can mess up date grouping. If that's what is happening, there is an argument that tells it to group on just the day vs the whole field value.

Good luck,
Tranman

Adam was not alone in the Garden of Eden, however,...much is due to Eve,
the first woman, and Satan, the first consultant.
Mark Twain

 

[ProgramError]

It may be worth have the query show all the fields to see if they are infuencing the sort order too.


Program Error
Programmers do it one finger at a time!

 

[Mike555]

Thanks everyone for your replies. As it turned out the date field was actually formatted as a Text field on the table. After updating the table to change the format from Text to Date, the sort works. Thanks everyone for your help!


--
Not until I became a Network Administrator did the error message "See your Network Administrator for assistance" become petrifying.