Sort by date in group is not working

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Adam Thwaites - 31 Jan 2006 12:49 GMT

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?

Signature

Adam Thwaites
Access Database Designer
adam.*spamless*thwaites@mercedes.co.uk
Manchester, UK

Reply to this Message

Marshall Barton - 31 Jan 2006 13:12 GMT

>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.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Adam Thwaites - 31 Jan 2006 13:27 GMT

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.

Signature

Adam Thwaites
Access Database Designer
adam.*spamless*thwaites@mercedes.co.uk
Manchester, UK

> >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
[quoted text clipped - 11 lines]
> Another concern is that the dates must be in a Date/Time
> type field, not a Text type field.

Reply to this Message

Marshall Barton - 31 Jan 2006 14:20 GMT

>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
[quoted text clipped - 7 lines]
>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.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Adam Thwaites - 31 Jan 2006 15:16 GMT

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

Signature

Adam Thwaites
Access Database Designer
adam.*spamless*thwaites@mercedes.co.uk
Manchester, UK

> >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
[quoted text clipped - 23 lines]
> about what you do have in Sorting and Grouping and a short
> example of what the report output looks like.

Reply to this Message

Marshall Barton - 31 Jan 2006 17:19 GMT

>The type of the date field is date.
>
[quoted text clipped - 28 lines]
>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

Signature

Marsh
MVP [MS Access]

Reply to this Message

Adam Thwaites - 31 Jan 2006 17:32 GMT

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?

Signature

Adam Thwaites
Access Database Designer
adam.*spamless*thwaites@mercedes.co.uk
Manchester, UK
(I have no access to other sites apart from microsoft.com so posting
external links is no use to me)

> >The type of the date field is date.
> >
[quoted text clipped - 35 lines]
> Group Header: No
> Groop Footer: No

Reply to this Message

Marshall Barton - 31 Jan 2006 18:26 GMT

>Wicked. Thats worked.
>
[quoted text clipped - 10 lines]
>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)

Signature

Marsh
MVP [MS Access]

Reply to this Message

Adam Thwaites - 31 Jan 2006 18:52 GMT

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.

Signature

Adam Thwaites
Access Database Designer
adam.*spamless*thwaites@mercedes.co.uk
Manchester, UK
(I have no access to other sites apart from microsoft.com so posting
external links is no use to me)

> >Wicked. Thats worked.
> >
[quoted text clipped - 14 lines]
>
> ="Week beginning: " & DatePart("ww", txtTheDate)

Reply to this Message