Report Grouping Ruining Detail Sort

[Ken M via AccessMonster.com]

I have a report of an audit trail of logins. The Query does not sort but the
records are in TIMESTAMP order.

Display fields:
[TIMESTAMP] in mm/dd/yyyy hh:nn:ss AM/PM (Default format no Format command
necessary)
[USERID]
[DOW] reformated from [TIMESTAMP] - Format([TIMESTAMP],"ddd")
[TimeRange]: Select Criteria: Format([TIMESTAMP],"Short Date")

I generate a report based on this Query. If I only have the report Page
Header and Detail with the Detail containg the [DOW] [TIMESTAMP] [USERID]
fields all records sort in Ascending order starting from 12:00 AM. However as
soon as I create a grouping TIMESTAMP - Ascending and add a Group Header that
Groups on Hour with a Group Interval of 1, the Detail Sort no longer sorts
correctly within each hourly grouping. (Grouping on Each Value does not
affect the Detail Sort)

I have tried working with the Query, the Group On (Hour,Day, Week), and Group
Interval with no luck.

Any suggestions would be helpfull.

 

[Duane Hookom]

What exactly are your sorting and grouping levels? Can provide something
like:

Field/Expression Other Properties
============= ===============
=DateValue([TIMESTAMP])
=....
[TIMESTAMP]

 

[Ken M via AccessMonster.com]

I finally got the report to group and sort correctly:

Here is the Query:

SELECT Format([TIMESTAMP],"ddd") AS DOW,
Format([TIMESTAMP],"Short Date") AS DT,
Format([TIMESTAMP],"hh") AS HRS,
USERNAME,
TIMESTAMP
FROM AUDIT_SESSION
WHERE TIMESTAMP Between [txt_StartDt] And [Txt_EndDt];

The Report Fields are:
------------------------------------------------------------------------------
------
HRS Header:
[DOW] [DT] [HRS]&":00"
Detail:
[TIMESTAMP] [USERNAME]
HRS Footer:

DT Footer:
------------------------------------------------------------------------------
------

In the report the Groupings are:

Group1: DT Ascending
Group Header - NO
Group Footer - YES
Group On - Each Value
Group Interval - 1

Group2: HRS Ascending
Group Header - YES
Group Footer - YES
Group On - Prefix Characters
Group Interval - 2

Group3: TIMESTAMP Ascending
Group Header and Footer - NO
Group On - Each Value
Group Interval - 1

The report now will break on each Day and Each hour and maintain the
TIMESTAMP Sort.

What exactly are your sorting and grouping levels? Can provide something
like:

Field/Expression Other Properties
============= ===============
=DateValue([TIMESTAMP])
=....
[TIMESTAMP]

I have a report of an audit trail of logins. The Query does not sort but
the

[quoted text clipped - 23 lines]

Any suggestions would be helpfull.

 

[Duane Hookom]

You can also use expressions in the Sorting and Grouping dialog in report
design.
=Format([TIMESTAMP],"ddd")
Make sure you place "=" at the beginning of your expressions.

--
Duane Hookom
MS Access MVP
--

I finally got the report to group and sort correctly:

Here is the Query:

SELECT Format([TIMESTAMP],"ddd") AS DOW,
Format([TIMESTAMP],"Short Date") AS DT,
Format([TIMESTAMP],"hh") AS HRS,
USERNAME,
TIMESTAMP
FROM AUDIT_SESSION
WHERE TIMESTAMP Between [txt_StartDt] And [Txt_EndDt];

The Report Fields are:
------------------------------------------------------------------------------
------
HRS Header:
[DOW] [DT] [HRS]&":00"
Detail:
[TIMESTAMP]
[USERNAME]
HRS Footer:

DT Footer:
------------------------------------------------------------------------------
------

In the report the Groupings are:

Group1: DT Ascending
Group Header - NO
Group Footer - YES
Group On - Each Value
Group Interval - 1

Group2: HRS Ascending
Group Header - YES
Group Footer - YES
Group On - Prefix Characters
Group Interval - 2

Group3: TIMESTAMP Ascending
Group Header and Footer - NO
Group On - Each Value
Group Interval - 1

The report now will break on each Day and Each hour and maintain the
TIMESTAMP Sort.

What exactly are your sorting and grouping levels? Can provide something
like:

Field/Expression Other Properties
============= ===============
=DateValue([TIMESTAMP])
=....
[TIMESTAMP]

I have a report of an audit trail of logins. The Query does not sort but
the

[quoted text clipped - 23 lines]

Any suggestions would be helpfull.