Background:

The database that I am working with is very poorly designed.  It is an Access table that records the filling of drugs from an automatic machine - however, if 500 Tylenol are filled on 3/2 at 9am, there will be 500 identical lines recorded in the database, rather than just one line with a column that says "500 filled."  Because of this, when I am trying to write a report, I have to group the report by date and then time in order to show the total of that particular fill.

 

Problem:

When I group by date, it groups randomly.  For example, the group header will say 12/30 but then it will capture a few days worth, and then the next one will say like 1/6 and that will capture a few days worth.  I tried to change it so the group was a formula -

              totext(datevalue({date_filled}))

and then that showed all of the correct group headings but of course then the dates don't sort completely chronologically.  The real kicker is that if i then change it BACK to just sorting by the date, then it sorts correctly AND groups correctly.  However, when I close Crystal and then open the report back up, it goes back to the same problem where the group header is capturing a few days worth of data.

 

Any ideas???