ACC1x: Report Grouping on Date/Time Field Incorrect

ID: Q109326

The information in this article applies to:

SYMPTOMS

A report that groups on a Date/Time field will group incorrectly if Group On is set to Day and the Interval is set to 1. The report will group the AM and PM records in different groups, instead of putting all the records for one day in one group. In some cases, the group header will be repeated.


RESOLUTION

To get the correct grouping on the report, set Group On to Hour and set the Interval to 24.


STATUS

Microsoft has confirmed this to be a problem in Microsoft Access versions 1.0 and 1.1. This problem no longer occurs in Microsoft Access version 2.0.


MORE INFORMATION

Steps to Reproduce Problem


  1. Copy the Orders table in the sample database NWIND.MDB to a table named Orders1.


  2. Open the Orders1 table in Datasheet view. Modify the entries in the Order Date column as follows, making sure to add "AM" and "PM" (without the quotation marks) as indicated: 
    
       Order ID      Order Date
       -------------------------------
       10000         1/6/92 9:00:00 AM
       10001         1/6/92 3:00:00 PM
       10002         1/7/92 9:00:00 AM
       10003         1/7/92 3:00:00 PM
       10004         1/7/92 3:00:00 PM


  3. Delete all other records in the table.


  4. Create a new report based on the Orders1 table.


  5. Choose ReportWizards.


  6. Select Groups/Totals, and then choose Next.


  7. Select Order ID and then choose ">". Select Order Date and then choose ">". Choose Next.


  8. Select Order Date and then choose ">". Choose Next.


  9. Select Day in the Records Will Be Grouped By box, and then choose Next.


  10. Choose Next.


  11. Choose Next.


  12. Choose Print Preview.


Note that January 6 is split into two groups. One group contains the AM records for January 6, and the other group contains the PM records for January 6 and the AM records for January 7. 


Keywords          : kbusage RptSort 
Version           : 1.0 1.1
Platform          : WINDOWS 
Issue type        : kbbug

Last Reviewed: March 30, 1999