A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Displaying dates by weeks in Report



 
 
Thread Tools Display Modes
  #11  
Old June 1st, 2010, 12:47 AM posted to microsoft.public.access
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Displaying dates by weeks in Report

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/For...ccess/201005/1

  #12  
Old June 2nd, 2010, 12:51 PM posted to microsoft.public.access
Alaska1
external usenet poster
 
Posts: 46
Default Displaying dates by weeks in Report

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/For...ccess/201005/1

.

  #13  
Old June 2nd, 2010, 04:28 PM posted to microsoft.public.access
Alaska1
external usenet poster
 
Posts: 46
Default Displaying dates by weeks in Report

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/For...ccess/201005/1

.

  #14  
Old June 2nd, 2010, 06:55 PM posted to microsoft.public.access
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Displaying dates by weeks in Report

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:28 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.