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 09:10
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: Tom van Stiphout on 28 May 2010 09:34
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 10:43
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: KARL DEWEY on 28 May 2010 12:43
>> 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 12:58
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

 |  Next  |  Last
Pages: 1 2 3
Prev: Which forum or website are the regulars switching to?
Next: Final Reminder - Microsoft Responds to the Evolution of Community