Monday --> Sunday

[Drum2001]

Currently I am running the following query:

SELECT DateFilter.Employee, DateFilter.TimeTypeIn,
sectodur(IIf(Sum(DateDiff("s",[clockin],[clockout]))-40*60*60>0,Sum(DateDiff("s",[clockin],[clockout]))-40*60*60,0))
AS Overtime, DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1 AS
WeekOf
FROM DateFilter
GROUP BY DateFilter.Employee, DateFilter.TimeTypeIn,
DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1
HAVING (((DateFilter.TimeTypeIn)="Regular"));
This is designed to calculate overtime. If someone's hours are over
40, it will calculate the number of hours over 40.

Currently it calculates the week from Sunday though Saturday. Is there
anyway to have it calculate from Monday through Sunday?

I know the "-Weekday()" command by default begins Monday. Is there a
way to modify this or is there another 'function' that begins on
Sunday?

Any help would be great!

Thank you!

[Linda Burnside]

Here is an example of a similar problem I solved some time ago. In my
example, the week started on Monday:

SELECT tblEmployee.EmpID, [EmpFirstName] & " " & [EmpLastName] AS EmpName,
tblTimeEntry.EmpJobSite, tblJobSite.JobSite, tblJobSite.JobAddress,
tblJobSite.JobCity, tblJobSite.JobState, tblJobSite.JobZip,
tblJobSite.JobStartDate, tblJobSite.JobEndDate, tblJobSite.JobSiteName,
tblJobSite.JobSiteClientCode, Format([InTimeStamp],"ww",2) AS weekNum
FROM tblJobSite INNER JOIN (tblEmployee INNER JOIN tblTimeEntry ON
tblEmployee.EmpID = tblTimeEntry.EmpID) ON tblJobSite.JobID =
tblTimeEntry.EmpJobSite
GROUP BY tblEmployee.EmpID, [EmpFirstName] & " " & [EmpLastName],
tblTimeEntry.EmpJobSite, tblJobSite.JobSite, tblJobSite.JobAddress,
tblJobSite.JobCity, tblJobSite.JobState, tblJobSite.JobZip,
tblJobSite.JobStartDate, tblJobSite.JobEndDate, tblJobSite.JobSiteName,
tblJobSite.JobSiteClientCode, Format([InTimeStamp],"ww",2)
HAVING (((Format([InTimeStamp],"ww",2)) Between
Format([Forms]![frmReports]![txtStartDate],"ww",2) And
Format([Forms]![frmReports]![txtEndDate],"ww",2)));
The part of the query above which makes the week start on Monday is

Format([InTimeStamp],"ww",2) AS weekNum
Linda

"Drum2001" <dr******@gmail.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...

Currently I am running the following query:

SELECT DateFilter.Employee, DateFilter.TimeTypeIn,
sectodur(IIf(Sum(DateDiff("s",[clockin],[clockout]))-40*60*60>0,Sum(DateDiff("s",[clockin],[clockout]))-40*60*60,0))
AS Overtime, DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1 AS
WeekOf
FROM DateFilter
GROUP BY DateFilter.Employee, DateFilter.TimeTypeIn,
DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1
HAVING (((DateFilter.TimeTypeIn)="Regular"));
This is designed to calculate overtime. If someone's hours are over
40, it will calculate the number of hours over 40.

Currently it calculates the week from Sunday though Saturday. Is there
anyway to have it calculate from Monday through Sunday?

I know the "-Weekday()" command by default begins Monday. Is there a
way to modify this or is there another 'function' that begins on
Sunday?

Any help would be great!

Thank you!

[Drum2001]

Thank you for your response!

How would I put this into my code? I dont understand the logic of the
function.


Linda Burnside wrote:

Here is an example of a similar problem I solved some time ago. In my
example, the week started on Monday:

SELECT tblEmployee.EmpID, [EmpFirstName] & " " & [EmpLastName] AS EmpName,
tblTimeEntry.EmpJobSite, tblJobSite.JobSite, tblJobSite.JobAddress,
tblJobSite.JobCity, tblJobSite.JobState, tblJobSite.JobZip,
tblJobSite.JobStartDate, tblJobSite.JobEndDate, tblJobSite.JobSiteName,
tblJobSite.JobSiteClientCode, Format([InTimeStamp],"ww",2) AS weekNum
FROM tblJobSite INNER JOIN (tblEmployee INNER JOIN tblTimeEntry ON
tblEmployee.EmpID = tblTimeEntry.EmpID) ON tblJobSite.JobID =
tblTimeEntry.EmpJobSite
GROUP BY tblEmployee.EmpID, [EmpFirstName] & " " & [EmpLastName],
tblTimeEntry.EmpJobSite, tblJobSite.JobSite, tblJobSite.JobAddress,
tblJobSite.JobCity, tblJobSite.JobState, tblJobSite.JobZip,
tblJobSite.JobStartDate, tblJobSite.JobEndDate, tblJobSite.JobSiteName,
tblJobSite.JobSiteClientCode, Format([InTimeStamp],"ww",2)
HAVING (((Format([InTimeStamp],"ww",2)) Between
Format([Forms]![frmReports]![txtStartDate],"ww",2) And
Format([Forms]![frmReports]![txtEndDate],"ww",2)));
The part of the query above which makes the week start on Monday is

Format([InTimeStamp],"ww",2) AS weekNum
Linda

"Drum2001" <dr******@gmail.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...

Currently I am running the following query:

SELECT DateFilter.Employee, DateFilter.TimeTypeIn,
sectodur(IIf(Sum(DateDiff("s",[clockin],[clockout]))-40*60*60>0,Sum(DateDiff("s",[clockin],[clockout]))-40*60*60,0))
AS Overtime, DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1 AS
WeekOf
FROM DateFilter
GROUP BY DateFilter.Employee, DateFilter.TimeTypeIn,
DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1
HAVING (((DateFilter.TimeTypeIn)="Regular"));
This is designed to calculate overtime. If someone's hours are over
40, it will calculate the number of hours over 40.

Currently it calculates the week from Sunday though Saturday. Is there
anyway to have it calculate from Monday through Sunday?

I know the "-Weekday()" command by default begins Monday. Is there a
way to modify this or is there another 'function' that begins on
Sunday?

Any help would be great!

Thank you!

[Drum2001]

Could someone explain in words what this is doing?

WeekOf: DateAdd("d",-Weekday([ClockInDate])+1,[ClockInDate])

ClockInDate is a Time/Date field for a Date stamp for when users "punch
in".

Drum2001 wrote:

Thank you for your response!

How would I put this into my code? I dont understand the logic of the
function.


Linda Burnside wrote:

Here is an example of a similar problem I solved some time ago. In my
example, the week started on Monday:

SELECT tblEmployee.EmpID, [EmpFirstName] & " " & [EmpLastName] AS EmpName,
tblTimeEntry.EmpJobSite, tblJobSite.JobSite, tblJobSite.JobAddress,
tblJobSite.JobCity, tblJobSite.JobState, tblJobSite.JobZip,
tblJobSite.JobStartDate, tblJobSite.JobEndDate, tblJobSite.JobSiteName,
tblJobSite.JobSiteClientCode, Format([InTimeStamp],"ww",2) AS weekNum
FROM tblJobSite INNER JOIN (tblEmployee INNER JOIN tblTimeEntry ON
tblEmployee.EmpID = tblTimeEntry.EmpID) ON tblJobSite.JobID =
tblTimeEntry.EmpJobSite
GROUP BY tblEmployee.EmpID, [EmpFirstName] & " " & [EmpLastName],
tblTimeEntry.EmpJobSite, tblJobSite.JobSite, tblJobSite.JobAddress,
tblJobSite.JobCity, tblJobSite.JobState, tblJobSite.JobZip,
tblJobSite.JobStartDate, tblJobSite.JobEndDate, tblJobSite.JobSiteName,
tblJobSite.JobSiteClientCode, Format([InTimeStamp],"ww",2)
HAVING (((Format([InTimeStamp],"ww",2)) Between
Format([Forms]![frmReports]![txtStartDate],"ww",2) And
Format([Forms]![frmReports]![txtEndDate],"ww",2)));
The part of the query above which makes the week start on Monday is

Format([InTimeStamp],"ww",2) AS weekNum
Linda

"Drum2001" <dr******@gmail.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...

Currently I am running the following query:
>
SELECT DateFilter.Employee, DateFilter.TimeTypeIn,
sectodur(IIf(Sum(DateDiff("s",[clockin],[clockout]))-40*60*60>0,Sum(DateDiff("s",[clockin],[clockout]))-40*60*60,0))
AS Overtime, DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1 AS
WeekOf
FROM DateFilter
GROUP BY DateFilter.Employee, DateFilter.TimeTypeIn,
DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1
HAVING (((DateFilter.TimeTypeIn)="Regular"));
>
>
This is designed to calculate overtime. If someone's hours are over
40, it will calculate the number of hours over 40.
>
Currently it calculates the week from Sunday though Saturday. Is there
anyway to have it calculate from Monday through Sunday?
>
I know the "-Weekday()" command by default begins Monday. Is there a
way to modify this or is there another 'function' that begins on
Sunday?
>
Any help would be great!
>
Thank you!
>

[Ron2006]

WeekOf is the field being calculated

The Dateadd function says to add something to a date - The date being
added to is the field at the end ([ClockInDate])

The "d" says to add days to the date

The expression "-Weekday([ClockInDate])+1" is computing how many days
to add. (This number can be negative - in which case it is actually
subtracting days from the date)

Weekday([ClockInDate]) says give me the day of the week. (July 4
which is a Tuesday would give me a 3)

If the Clockindate is July 4, 2006
The "-Weekday([ClockInDate])+1" expressing is evaluated as
follows:

((-1) * 3) + 1
-3 + 1
-2

consequently

WeekOf: DateAdd("d",-Weekday([ClockInDate])+1,[ClockInDate])

evaluates to DateAdd("d",-2,07/04/2006) {{Subtract
2 days from 7/4/6}}

WeekOf: 07/02/2006

The formula will always calculate the Sunday of the week in which the
date exists.

Ron

[Drum2001]

Got it Ron2006!

Thank you!
Ron2006 wrote:

WeekOf is the field being calculated

The Dateadd function says to add something to a date - The date being
added to is the field at the end ([ClockInDate])

The "d" says to add days to the date

The expression "-Weekday([ClockInDate])+1" is computing how many days
to add. (This number can be negative - in which case it is actually
subtracting days from the date)

Weekday([ClockInDate]) says give me the day of the week. (July 4
which is a Tuesday would give me a 3)

If the Clockindate is July 4, 2006
The "-Weekday([ClockInDate])+1" expressing is evaluated as
follows:

((-1) * 3) + 1
-3 + 1
-2

consequently

WeekOf: DateAdd("d",-Weekday([ClockInDate])+1,[ClockInDate])

evaluates to DateAdd("d",-2,07/04/2006) {{Subtract
2 days from 7/4/6}}

WeekOf: 07/02/2006

The formula will always calculate the Sunday of the week in which the
date exists.

Ron