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: KenSheridan via AccessMonster.com on 31 May 2010 19:47 That's because your newsreader has split the line over two. It should all be a single line. This is something you need to watch out form when code is posted here. However, you must include the starting day of the working week, which in your case is a Monday, so the value to be passed into the function is 2: Week: WeekStart(2,[CompletedandReturnedDate]) The second argument is optional if you want to return the start date for the week of the current date, but in your case it's the starting date of the week for the CompletedandReturnedDate so you need both arguments. I notice that some of the code is repeated in your last post?? The function should be pasted into a module exactly as I posted it, and the spurious line break in the first line removed: 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 Ken Sheridan Stafford, England Alaska1 wrote: >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. > >> 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 >[quoted text clipped - 25 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 2 Jun 2010 07:51 Thank you for all your help. It worked fine. I moved the as variant up to the first line and added the 2 in the query. Appreciate all your help. "KenSheridan via AccessMonster.com" wrote: > That's because your newsreader has split the line over two. It should all be > a single line. This is something you need to watch out form when code is > posted here. > > However, you must include the starting day of the working week, which in your > case is a Monday, so the value to be passed into the function is 2: > > Week: WeekStart(2,[CompletedandReturnedDate]) > > The second argument is optional if you want to return the start date for the > week of the current date, but in your case it's the starting date of the week > for the CompletedandReturnedDate so you need both arguments. > > I notice that some of the code is repeated in your last post?? The function > should be pasted into a module exactly as I posted it, and the spurious line > break in the first line removed: > > 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 > > Ken Sheridan > Stafford, England > > Alaska1 wrote: > >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. > > > >> 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 > >[quoted text clipped - 25 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 2 Jun 2010 11:28 Just one more question. I have to group the date by the week and by person. What is the best way to do it? 4/19/2010 Tom Smith 15 returns "KenSheridan via AccessMonster.com" wrote: > That's because your newsreader has split the line over two. It should all be > a single line. This is something you need to watch out form when code is > posted here. > > However, you must include the starting day of the working week, which in your > case is a Monday, so the value to be passed into the function is 2: > > Week: WeekStart(2,[CompletedandReturnedDate]) > > The second argument is optional if you want to return the start date for the > week of the current date, but in your case it's the starting date of the week > for the CompletedandReturnedDate so you need both arguments. > > I notice that some of the code is repeated in your last post?? The function > should be pasted into a module exactly as I posted it, and the spurious line > break in the first line removed: > > 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 > > Ken Sheridan > Stafford, England > > Alaska1 wrote: > >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. > > > >> 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 > >[quoted text clipped - 25 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: KenSheridan via AccessMonster.com on 2 Jun 2010 13:55
To show each week and then the rows for each week for that person then group first on WeekStart and then on EmployeeID. Give the WeekStart group a group header. Whether you also give the EmployeeID a group header depends on whether you want the name as a heading for a group of detail rows for each employee or not. Grouping on a unique value like EmployeeID ensures that no two employees with the same name are lumped together (I worked with two Maggie Taylors once), but it sorts the rows by the ID number not alphabetically by name. To sort by name first group the report by WeekStart, then by the name, and then by the EmployeeID, but give the EmployeeID group a group header, not the name group if you want a heading for each person's rows. The report will then be ordered within each week by the name, but each employee will be reported separately even if they have exactly the same name. Ken Sheridan Stafford, England Alaska1 wrote: >Just one more question. I have to group the date by the week and by person. >What is the best way to do it? > >4/19/2010 >Tom Smith 15 returns > >> That's because your newsreader has split the line over two. It should all be >> a single line. This is something you need to watch out form when code is >[quoted text clipped - 81 lines] >> >> >> >> >5/24/2010 - 5/28/2010 5 time sheets returned >> >> >> >> . -- Message posted via http://www.accessmonster.com |