Sort by date in group is not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report which is sorted by date and split into week groups. For some
reason the dates are always coming up descending irrelevant of what i'v
selected in the recordsource query or in the Sorting and Grouping box. What
is wrong with it?
 
Adam said:
I have a report which is sorted by date and split into week groups. For some
reason the dates are always coming up descending irrelevant of what i'v
selected in the recordsource query or in the Sorting and Grouping box. What
is wrong with it?
Click to expand...


I can't tell what's wrong with "it" unless you tell us what
"it" is.

Note that sorting the query is a waste of time, report
sorting is specified in Sorting and Grouping. In this case,
it sounds like you need two entries, the first one for the
week group and the second one on the date field.

Another concern is that the dates must be in a Date/Time
type field, not a Text type field.
 
The database shows the times staff have gone and come back from breaks. Every
day they login a new record is created and the break times updates as the day
goes by.

This specific report is an individual user one which shows the users times
for the current month. The report is split into weeks so at the bottom of
each week the user can see how much time they owe or are owed.

The date field is in date format and as the Sorting and Grouping has split
the data into weeks for me there seems no need to have an extra field for
this.
 
Adam said:
The database shows the times staff have gone and come back from breaks. Every
day they login a new record is created and the break times updates as the day
goes by.

This specific report is an individual user one which shows the users times
for the current month. The report is split into weeks so at the bottom of
each week the user can see how much time they owe or are owed.

The date field is in date format and as the Sorting and Grouping has split
the data into weeks for me there seems no need to have an extra field for
this.
Click to expand...


The format or how the date appears on the screen is
irrelevant. The important thing is the **type** of the
field.

You said that the dates are sorted descending, but then you
say you are grouping by week. Does that mean the weeks are
sorted ascending, but the dates in the week are descending.
If so, that's why I said you need a second entry in Sorting
and Grouping for the date field. Again, the sorting in the
query is not used in the report, so that will have no
effect.

If that's not what you mean, please provide more details
about what you do have in Sorting and Grouping and a short
example of what the report output looks like.
 
The type of the date field is date.

Date In Time Out Time Morning Break Lunch Break Afternoon Break TOTAL
Start Stop Start Stop Start Stop
Week beginning: 16/01/2006
20/01/2006 09:59 18:24 11:45 11:59 15:00 15:32 17:38 17:38 00:46:30
00:46:30
Week beginning: 23/01/2006
27/01/2006 08:57 18:05 11:42 11:59 15:35 16:19 00:00 00:00 01:01:29
26/01/2006 08:59 18:00 11:40 11:58 15:14 15:44 17:20 17:34 01:02:38
25/01/2006 08:57 18:00 11:44 12:06 14:44 15:25 00:00 00:00 01:03:30
24/01/2006 08:58 18:03 11:01 11:21 15:01 15:42 00:00 00:00 01:00:35
23/01/2006 09:06 18:01 11:17 11:30 14:39 15:07 16:17 16:35 00:59:31
05:07:43
Week beginning: 23/01/2006
30/01/2006 09:58 18:59 12:27 12:39 15:00 15:26 00:00 00:00 00:37:49
00:37:49

This is a basic idea of what the report looks like at the moment. I hope it
displays properly.
As you can see the weeks are in ascending order but the days are descending.

The Sorting and Grouping options are as follows:

Field/Expression: Date (Bad move to call the field this which I will retify
asap)
Sort Order: Ascending
Group Header: Yes
Groop Footer: Yes
Group On: Week
Group Interval: 1
Keep Togeather: Whole Group
 
Adam said:
The type of the date field is date.

Date In Time Out Time Morning Break Lunch Break Afternoon Break TOTAL
Start Stop Start Stop Start Stop
Week beginning: 16/01/2006
20/01/2006 09:59 18:24 11:45 11:59 15:00 15:32 17:38 17:38 00:46:30
00:46:30
Week beginning: 23/01/2006
27/01/2006 08:57 18:05 11:42 11:59 15:35 16:19 00:00 00:00 01:01:29
26/01/2006 08:59 18:00 11:40 11:58 15:14 15:44 17:20 17:34 01:02:38
25/01/2006 08:57 18:00 11:44 12:06 14:44 15:25 00:00 00:00 01:03:30
24/01/2006 08:58 18:03 11:01 11:21 15:01 15:42 00:00 00:00 01:00:35
23/01/2006 09:06 18:01 11:17 11:30 14:39 15:07 16:17 16:35 00:59:31
05:07:43
Week beginning: 23/01/2006
30/01/2006 09:58 18:59 12:27 12:39 15:00 15:26 00:00 00:00 00:37:49
00:37:49

This is a basic idea of what the report looks like at the moment. I hope it
displays properly.
As you can see the weeks are in ascending order but the days are descending.

The Sorting and Grouping options are as follows:

Field/Expression: Date (Bad move to call the field this which I will retify
asap)
Sort Order: Ascending
Group Header: Yes
Groop Footer: Yes
Group On: Week
Group Interval: 1
Keep Togeather: Whole Group
Click to expand...


Add the second entry in Sorting and Grouping !!!

Field/Expression: Date
Sort Order: Ascending
Group Header: No
Groop Footer: No
 
Wicked. Thats worked.

Thanks for you help Marsh.

One last one you might be able to help with, on the report sample you can
see the 'Week beginning:...' text. At the moment I populate it with code:

Private Sub FooterDate_Format(Cancel As Integer, FormatCount As Integer)
Me.txtWeekBeginning = "Week beginning: " & txtTheDate 'from the first date
field
End Sub

But this is populating the field with random dates form the table. Do you
know how I would get this to work properly?
--
Adam Thwaites
Access Database Designer
adam.*spamless*[email protected]
Manchester, UK
(I have no access to other sites apart from microsoft.com so posting
external links is no use to me)
 
Adam said:
Wicked. Thats worked.

Thanks for you help Marsh.

One last one you might be able to help with, on the report sample you can
see the 'Week beginning:...' text. At the moment I populate it with code:

Private Sub FooterDate_Format(Cancel As Integer, FormatCount As Integer)
Me.txtWeekBeginning = "Week beginning: " & txtTheDate 'from the first date
field
End Sub

But this is populating the field with random dates form the table. Do you
know how I would get this to work properly?
Click to expand...

Scrap the code and use an expression in the text box:

="Week beginning: " & DatePart("ww", txtTheDate)
 
Thanks, i'v never used the DataPart code before. I ended up with this
solution for the control source of the text box:

="Week: " & DatePart("ww",[TheDate]) & " - Beginning: " &
Format(([TheDate]-Weekday([TheDate],1)+2),"dd" & "/" & "mm")


Thanks for all your help Marsh.


--
Adam Thwaites
Access Database Designer
adam.*spamless*[email protected]
Manchester, UK
(I have no access to other sites apart from microsoft.com so posting
external links is no use to me)
 
Back
Top