Displaying dates by weeks in Report
in [General]
|
Prev: Which forum or website are the regulars switching to?
Next: Final Reminder - Microsoft Responds to the Evolution of Community
From: Alaska1 on 28 May 2010 15:07 Thank you it work and i can group on the week but in a report it will read 2010-21 "KARL DEWEY" wrote: > >> Is that a built in function in access? > Yes. > Format([YourDateTimeField], "yyyy-ww") > > For the following dates you get -- > entrydate Year-Wk > 5/22/2010 2010-21 > 5/23/2010 2010-22 > 5/24/2010 2010-22 > 5/25/2010 2010-22 > 5/26/2010 2010-22 > 5/27/2010 2010-22 > 5/28/2010 2010-22 > 5/29/2010 2010-22 > 5/30/2010 2010-23 > 5/31/2010 2010-23 > > This is based on Sunday being the first day of the week but some payroll > systems have the week starting on other days. > > To use week starting on Saturday use this -- > Format([entrydate]+1,"yyyy-ww") > > -- > Build a little, test a little. > > > "Alaska1" wrote: > > > Is that a built in function in access? > > > > "Tom van Stiphout" wrote: > > > > > On Fri, 28 May 2010 06:10:01 -0700, Alaska1 > > > <Alaska1(a)discussions.microsoft.com> wrote: > > > > > > I use a function that turns a date into a weeknumber in the format > > > yyyy-ww, which I can then group by. The function uses the Format > > > function to do this conversion. > > > > > > -Tom. > > > Microsoft Access MVP > > > > > > > > > >I have to display data by weeks in a report. What is the best way to do it? > > > > > > > >weeks > > > >5/24/2010 - 5/28/2010 5 time sheets returned > > > . > > >
From: Alaska1 on 28 May 2010 15:16 Thank you for taking the time to answer my question. The Format([The Date], "yyyy-mm") would work to sum and group the date but 2010-17 is not going to be familiar to those looking at the report. I will try your public function. Just not sure, I am doing the function in the report or the query? "KenSheridan via AccessMonster.com" wrote: > The Format function is, so you could call it directly with: > > Format([The Date], "yyyy-mm") > > Or you could wrap that in a custom function, which is what Tom is suggesting. > However, a more flexible solution is to return the week-starting date for a > date with a function like this: > > Public Function WeekStart(intStartDay As Integer, Optional varDate As Variant) > As Variant > > ' Returns 'week starting' date for any date > > ' Arguments: > ' 1. intStartDay - weekday on which week starts, 1-7 (Sun - Sat) > ' 2. vardate - optional date value for which week starting > ' date to be returned. Defaults to current date > > If IsMissing(varDate) Then varDate = VBA.Date > > If Not IsNull(varDate) Then > WeekStart = varDate - Weekday(varDate, intStartDay) + 1 > End If > > End Function > > This allows you to specify on which day the week starts (Monday by the look > of it in your case, so you'd pass 2 into the function as the first argument) > and defaults to the current date if no date is passed into the function. So, > calling it today, 2010-05-28: > > WeekStart(2) returns 24/05/2010 > > WeekStart(2,#2010-06-01#) returns 31/05/2010 > > or for Sunday as the week starting day: > WeekStart(1,#2010-06-01#) returns 30/05/2010 > > The return values here are in the dd/mm/yyyy UK format, but would be in > mm/dd/yyyy format on your system of course. > > You can call the function in the report's underlying query: > > WeekStarting:WeekStart(2,[YourDateField]) > > and then group the report on the WeekStarting column. > > Ken Sheridan > Stafford, England > > Alaska1 wrote: > >Is that a built in function in access? > > > >> I use a function that turns a date into a weeknumber in the format > >> yyyy-ww, which I can then group by. The function uses the Format > >[quoted text clipped - 8 lines] > >> >5/24/2010 - 5/28/2010 5 time sheets returned > >> . > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access/201005/1 > > . >
From: KARL DEWEY on 28 May 2010 16:07 Add one more field for display purposes -- Week_Period: [entrydate]-Weekday([entrydate]) & " - " & [entrydate]-Weekday([entrydate])+6 -- Build a little, test a little. "Alaska1" wrote: > Thank you it work and i can group on the week but in a report it will read > 2010-21 > > "KARL DEWEY" wrote: > > > >> Is that a built in function in access? > > Yes. > > Format([YourDateTimeField], "yyyy-ww") > > > > For the following dates you get -- > > entrydate Year-Wk > > 5/22/2010 2010-21 > > 5/23/2010 2010-22 > > 5/24/2010 2010-22 > > 5/25/2010 2010-22 > > 5/26/2010 2010-22 > > 5/27/2010 2010-22 > > 5/28/2010 2010-22 > > 5/29/2010 2010-22 > > 5/30/2010 2010-23 > > 5/31/2010 2010-23 > > > > This is based on Sunday being the first day of the week but some payroll > > systems have the week starting on other days. > > > > To use week starting on Saturday use this -- > > Format([entrydate]+1,"yyyy-ww") > > > > -- > > Build a little, test a little. > > > > > > "Alaska1" wrote: > > > > > Is that a built in function in access? > > > > > > "Tom van Stiphout" wrote: > > > > > > > On Fri, 28 May 2010 06:10:01 -0700, Alaska1 > > > > <Alaska1(a)discussions.microsoft.com> wrote: > > > > > > > > I use a function that turns a date into a weeknumber in the format > > > > yyyy-ww, which I can then group by. The function uses the Format > > > > function to do this conversion. > > > > > > > > -Tom. > > > > Microsoft Access MVP > > > > > > > > > > > > >I have to display data by weeks in a report. What is the best way to do it? > > > > > > > > > >weeks > > > > >5/24/2010 - 5/28/2010 5 time sheets returned > > > > . > > > >
From: KenSheridan via AccessMonster.com on 28 May 2010 16:23 Best to do it in the query as a computed column in the way I described in my last post. Then you can group on the column and include a text box in the group header with a control source such as: ="Week starting " & [WeekStarting] You can of course format the [WeekStarting] value in any way you like, e.g. ="Week starting " & Format([WeekStarting], "dddd mmmm dd yyyy") would give you a heading in the format: Monday May 31 2010 Ken Sheridan Stafford, England Alaska1 wrote: >Thank you for taking the time to answer my question. The Format([The Date], >"yyyy-mm") would work to sum and group the date but 2010-17 is not going to >be familiar to those looking at the report. > >I will try your public function. Just not sure, I am doing the function in >the report or the query? > >> The Format function is, so you could call it directly with: >> >[quoted text clipped - 53 lines] >> >> >5/24/2010 - 5/28/2010 5 time sheets returned >> >> . -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201005/1
From: Alaska1 on 31 May 2010 19:22
I have used the code you provided in the module calling it Function. I am getting an error on the second line As Variant Public Function WeekStart(intStartDay As Integer, Optional varDate As Variant) As Variant ' Returns 'week starting' date for any date ' Arguments: ' 1. intStartDay - weekday on which week starts, 1-7 (Sun - Sat) ' 2. vardate - optional date value for which week starting ' date to be returned. Defaults to current date If IsMissing(varDate) Then varDate = VBA.Date If Not IsNull(varDate) Then WeekStart = varDate - Weekday(varDate, intStartDay) + 1 End If Public Function WeekStart(intStartDay As Integer, Optional varDate As Variant) As Variant ' Returns 'week starting' date for any date ' Arguments: ' 1. intStartDay - weekday on which week starts, 1-7 (Sun - Sat) ' 2. vardate - optional date value for which week starting ' date to be returned. Defaults to current date If IsMissing(varDate) Then varDate = VBA.Date If Not IsNull(varDate) Then WeekStart = varDate - Weekday(varDate, intStartDay) + 1 End If I have added it into the query as a column Week: WeekStart([CompletedandReturnedDate]) with CompletedandReturnedDate having the date in that field. WeekStart is my public function name. I am getting an error in the query. It is not pulling any data for that field. "KenSheridan via AccessMonster.com" wrote: > Best to do it in the query as a computed column in the way I described in my > last post. Then you can group on the column and include a text box in the > group header with a control source such as: > > ="Week starting " & [WeekStarting] > > You can of course format the [WeekStarting] value in any way you like, e.g. > > ="Week starting " & Format([WeekStarting], "dddd mmmm dd yyyy") > > would give you a heading in the format: > > Monday May 31 2010 > > Ken Sheridan > Stafford, England > > Alaska1 wrote: > >Thank you for taking the time to answer my question. The Format([The Date], > >"yyyy-mm") would work to sum and group the date but 2010-17 is not going to > >be familiar to those looking at the report. > > > >I will try your public function. Just not sure, I am doing the function in > >the report or the query? > > > >> The Format function is, so you could call it directly with: > >> > >[quoted text clipped - 53 lines] > >> >> >5/24/2010 - 5/28/2010 5 time sheets returned > >> >> . > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access/201005/1 > > . > |