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
  #1  
Old May 28th, 2010, 02:10 PM posted to microsoft.public.access
Alaska1
external usenet poster
 
Posts: 46
Default Displaying dates by weeks in Report

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
  #2  
Old May 28th, 2010, 02:34 PM posted to microsoft.public.access
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default Displaying dates by weeks in Report

On Fri, 28 May 2010 06:10:01 -0700, Alaska1
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

  #3  
Old May 28th, 2010, 03:43 PM posted to microsoft.public.access
Alaska1
external usenet poster
 
Posts: 46
Default Displaying dates by weeks in Report

Is that a built in function in access?

"Tom van Stiphout" wrote:

On Fri, 28 May 2010 06:10:01 -0700, Alaska1
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

.

  #4  
Old May 28th, 2010, 05:43 PM posted to microsoft.public.access
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Displaying dates by weeks in Report

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

.

  #5  
Old May 28th, 2010, 05:58 PM posted to microsoft.public.access
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Displaying dates by weeks in Report

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

  #6  
Old May 28th, 2010, 08:07 PM posted to microsoft.public.access
Alaska1
external usenet poster
 
Posts: 46
Default Displaying dates by weeks in Report

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

  #7  
Old May 28th, 2010, 08:16 PM posted to microsoft.public.access
Alaska1
external usenet poster
 
Posts: 46
Default Displaying dates by weeks in Report

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

.

  #8  
Old May 28th, 2010, 09:07 PM posted to microsoft.public.access
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Displaying dates by weeks in Report

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

  #9  
Old May 28th, 2010, 09:23 PM posted to microsoft.public.access
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Displaying dates by weeks in Report

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

  #10  
Old June 1st, 2010, 12:22 AM posted to microsoft.public.access
Alaska1
external usenet poster
 
Posts: 46
Default Displaying dates by weeks in Report

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

.

 




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 05:39 PM.


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