Re: Monday --> Sunday

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" <drum2001@xxxxxxxxx> wrote in message
news:1152635890.076572.171230@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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!



.

Relevant Pages