Sort by date in group is not working

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 Thwaites
Access Database Designer

Manchester, UK

Adam Thwaites wrote:

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?


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.

--
Marsh
MVP [MS Access]

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 Thwaites
Access Database Designer

Manchester, UK


"Marshall Barton" wrote:

Adam Thwaites wrote:

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?


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.

--
Marsh
MVP [MS Access]

Adam Thwaites wrote:

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.


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.

--
Marsh
MVP [MS Access]

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 Thwaites
Access Database Designer

Manchester, UK

"Marshall Barton" wrote:

Adam Thwaites wrote:

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.


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.

--
Marsh
MVP [MS Access]

Adam Thwaites wrote:

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


Add the second entry in Sorting and Grouping !!!

Field/Expression: Date
Sort Order: Ascending
Group Header: No
Groop Footer: No

--
Marsh
MVP [MS Access]

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

Manchester, UK
(I have no access to other sites apart from microsoft.com so posting
external links is no use to me)


"Marshall Barton" wrote:

Adam Thwaites wrote:

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


Add the second entry in Sorting and Grouping !!!

Field/Expression: Date
Sort Order: Ascending
Group Header: No
Groop Footer: No

--
Marsh
MVP [MS Access]

Adam Thwaites wrote:

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?

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

="Week beginning: " & DatePart("ww", txtTheDate)

--
Marsh
MVP [MS Access]

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

Manchester, UK
(I have no access to other sites apart from microsoft.com so posting
external links is no use to me)


"Marshall Barton" wrote:

Adam Thwaites wrote:

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?

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

="Week beginning: " & DatePart("ww", txtTheDate)

--
Marsh
MVP [MS Access]