If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Sort by date in group is not working
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] |
#3
|
|||
|
|||
Sort by date in group is not working
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] |
#4
|
|||
|
|||
Sort by date in group is not working
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] |
#5
|
|||
|
|||
Sort by date in group is not working
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] |
#6
|
|||
|
|||
Sort by date in group is not working
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] |
#7
|
|||
|
|||
Sort by date in group is not working
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] |
#8
|
|||
|
|||
Sort by date in group is not working
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] |
#9
|
|||
|
|||
Sort by date in group is not working
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] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Query for 'confirmation' | rogge | Running & Setting Up Queries | 8 | April 19th, 2005 03:26 PM |
Ambiguous outer joins | Renwick | New Users | 1 | February 22nd, 2005 02:08 PM |
ambiguous outer joins | renwick | Running & Setting Up Queries | 3 | February 22nd, 2005 01:29 PM |
Syntax needed to get needed reports | Frank Lueder | New Users | 15 | January 6th, 2005 08:39 AM |
Does date fall between two ranges? | MR | Worksheet Functions | 4 | January 14th, 2004 04:08 PM |