DateAdd Shift

[Drum2001]

I have a report that acts as a time sheet for employees. It lists
there "punch-in" and "punch-out and catagorizes them by week, with
different totals..

At the end of each week is a total of all the hours for the week. The
pay week is from Monday -> Sunday, but with the query below, it is
totaling Sunday -> Saturday. Does anyone know how I could "shift" the
work week?

Any help would be great!!!!!!!!!

Thanks!

Brad

This query is TimeClockSum-Individual Week:

//
SELECT Sum(DateDiff("s ",[ClockIn],[ClockOut]))\3600 &
Format((Sum(Dat eDiff("s",[ClockIn],[ClockOut])) Mod
3600)/86400,":nn:ss") AS TotalTime,
DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1 AS WeekOf,
[ADateFilter - 1 Week].Employee, [ADateFilter - 1 Week].TimeTypeIn
FROM [ADateFilter - 1 Week]
GROUP BY DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1,
[ADateFilter - 1 Week].Employee, [ADateFilter - 1 Week].TimeTypeIn
HAVING ((([ADateFilter - 1 Week].TimeTypeIn)="R egular"));
//
ADateFilter - 1 Week is the following query:

//
SELECT TimeClockHistor y.TimeEventID, TimeClockHistor y.Employee,
TimeClockHistor y.ClockInDate, TimeClockHistor y.ClockIn,
TimeClockHistor y.ReasonClockIn , TimeClockHistor y.TimeTypeIn,
TimeClockHistor y.ClockOutDate, TimeClockHistor y.ClockOut,
TimeClockHistor y.ReasonClockOu t, TimeClockHistor y.TimeTypeOut
FROM TimeClockHistor y
WHERE (((TimeClockHis tory.Employee)=[forms]![EmployeeFilter -
Admin]![cboEmployee]) AND ((TimeClockHist ory.ClockInDate ) Between
[forms]![EmployeeFilter - Admin]![Date1]+2 And [forms]![EmployeeFilter
- Admin]![Date1]-11));
//

[salad]

Drum2001 wrote:

I have a report that acts as a time sheet for employees. It lists
there "punch-in" and "punch-out and catagorizes them by week, with
different totals..

At the end of each week is a total of all the hours for the week. The
pay week is from Monday -> Sunday, but with the query below, it is
totaling Sunday -> Saturday. Does anyone know how I could "shift" the
work week?

Any help would be great!!!!!!!!!

Thanks!

Brad

This query is TimeClockSum-Individual Week:

//
SELECT Sum(DateDiff("s ",[ClockIn],[ClockOut]))\3600 &
Format((Sum(Dat eDiff("s",[ClockIn],[ClockOut])) Mod
3600)/86400,":nn:ss") AS TotalTime,
DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1 AS WeekOf,
[ADateFilter - 1 Week].Employee, [ADateFilter - 1 Week].TimeTypeIn
FROM [ADateFilter - 1 Week]
GROUP BY DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1,
[ADateFilter - 1 Week].Employee, [ADateFilter - 1 Week].TimeTypeIn
HAVING ((([ADateFilter - 1 Week].TimeTypeIn)="R egular"));
//
ADateFilter - 1 Week is the following query:

//
SELECT TimeClockHistor y.TimeEventID, TimeClockHistor y.Employee,
TimeClockHistor y.ClockInDate, TimeClockHistor y.ClockIn,
TimeClockHistor y.ReasonClockIn , TimeClockHistor y.TimeTypeIn,
TimeClockHistor y.ClockOutDate, TimeClockHistor y.ClockOut,
TimeClockHistor y.ReasonClockOu t, TimeClockHistor y.TimeTypeOut
FROM TimeClockHistor y
WHERE (((TimeClockHis tory.Employee)=[forms]![EmployeeFilter -
Admin]![cboEmployee]) AND ((TimeClockHist ory.ClockInDate ) Between
[forms]![EmployeeFilter - Admin]![Date1]+2 And [forms]![EmployeeFilter
- Admin]![Date1]-11));
//

Don't know if this helps, but WeekDay takes an option second argument
that specifies the first day of the week. Use vbSunday/1 for Sunday as
the start of the week, vbMonday/2 for Monday ect.

[Drum2001]

salad wrote:

Drum2001 wrote:

I have a report that acts as a time sheet for employees. It lists
there "punch-in" and "punch-out and catagorizes them by week, with
different totals..

At the end of each week is a total of all the hours for the week. The
pay week is from Monday -> Sunday, but with the query below, it is
totaling Sunday -> Saturday. Does anyone know how I could "shift" the
work week?

Any help would be great!!!!!!!!!

Thanks!

Brad

This query is TimeClockSum-Individual Week:

//
SELECT Sum(DateDiff("s ",[ClockIn],[ClockOut]))\3600 &
Format((Sum(Dat eDiff("s",[ClockIn],[ClockOut])) Mod
3600)/86400,":nn:ss") AS TotalTime,
DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1 AS WeekOf,
[ADateFilter - 1 Week].Employee, [ADateFilter - 1 Week].TimeTypeIn
FROM [ADateFilter - 1 Week]
GROUP BY DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1,
[ADateFilter - 1 Week].Employee, [ADateFilter - 1 Week].TimeTypeIn
HAVING ((([ADateFilter - 1 Week].TimeTypeIn)="R egular"));
//
ADateFilter - 1 Week is the following query:

//
SELECT TimeClockHistor y.TimeEventID, TimeClockHistor y.Employee,
TimeClockHistor y.ClockInDate, TimeClockHistor y.ClockIn,
TimeClockHistor y.ReasonClockIn , TimeClockHistor y.TimeTypeIn,
TimeClockHistor y.ClockOutDate, TimeClockHistor y.ClockOut,
TimeClockHistor y.ReasonClockOu t, TimeClockHistor y.TimeTypeOut
FROM TimeClockHistor y
WHERE (((TimeClockHis tory.Employee)=[forms]![EmployeeFilter -
Admin]![cboEmployee]) AND ((TimeClockHist ory.ClockInDate ) Between
[forms]![EmployeeFilter - Admin]![Date1]+2 And [forms]![EmployeeFilter
- Admin]![Date1]-11));
//

Don't know if this helps, but WeekDay takes an option second argument
that specifies the first day of the week. Use vbSunday/1 for Sunday as
the start of the week, vbMonday/2 for Monday ect..

What would I put exatly and where. I replaced -Weekday with vbSunday/1
and recieved a syntax error. I then took out the "/1" and the query
ran, however, did not produce any results for this query now.

[fredg]

On 20 Jun 2006 07:57:10 -0700, Drum2001 wrote:

salad wrote:

Drum2001 wrote:

I have a report that acts as a time sheet for employees. It lists
there "punch-in" and "punch-out and catagorizes them by week, with
different totals..

At the end of each week is a total of all the hours for the week. The
pay week is from Monday -> Sunday, but with the query below, it is
totaling Sunday -> Saturday. Does anyone know how I could "shift" the
work week?

Any help would be great!!!!!!!!!

Thanks!

Brad

This query is TimeClockSum-Individual Week:

//
SELECT Sum(DateDiff("s ",[ClockIn],[ClockOut]))\3600 &
Format((Sum(Dat eDiff("s",[ClockIn],[ClockOut])) Mod
3600)/86400,":nn:ss") AS TotalTime,
DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1 AS WeekOf,
[ADateFilter - 1 Week].Employee, [ADateFilter - 1 Week].TimeTypeIn
FROM [ADateFilter - 1 Week]
GROUP BY DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1,
[ADateFilter - 1 Week].Employee, [ADateFilter - 1 Week].TimeTypeIn
HAVING ((([ADateFilter - 1 Week].TimeTypeIn)="R egular"));
//
ADateFilter - 1 Week is the following query:

//
SELECT TimeClockHistor y.TimeEventID, TimeClockHistor y.Employee,
TimeClockHistor y.ClockInDate, TimeClockHistor y.ClockIn,
TimeClockHistor y.ReasonClockIn , TimeClockHistor y.TimeTypeIn,
TimeClockHistor y.ClockOutDate, TimeClockHistor y.ClockOut,
TimeClockHistor y.ReasonClockOu t, TimeClockHistor y.TimeTypeOut
FROM TimeClockHistor y
WHERE (((TimeClockHis tory.Employee)=[forms]![EmployeeFilter -
Admin]![cboEmployee]) AND ((TimeClockHist ory.ClockInDate ) Between
[forms]![EmployeeFilter - Admin]![Date1]+2 And [forms]![EmployeeFilter
- Admin]![Date1]-11));
//

Don't know if this helps, but WeekDay takes an option second argument
that specifies the first day of the week. Use vbSunday/1 for Sunday as
the start of the week, vbMonday/2 for Monday ect..

What would I put exatly and where. I replaced -Weekday with vbSunday/1
and recieved a syntax error. I then took out the "/1" and the query
ran, however, did not produce any results for this query now.

My comments are regarding the use of the Weekday function only, not
whether your SQL will return the correct records.

Did you not read VBA help on the Weekday function?
vbSunday is the default week start, so at the very least, you would
need vbMonday.
Also, in a query, you cannot use the VB constants (as you are not
using VB). Therefore, you would need to use the constant's value. For
vbMonday, that would be 2, i.e. WeekDay([YourFieldName],2)

Read the help files!
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Drum2001]

Actually Fred,

I did read the help files, but I did not understand the explination.
That is why I asked on the forum.

Thank you for taking the time to correct me.
fredg wrote:

On 20 Jun 2006 07:57:10 -0700, Drum2001 wrote:

salad wrote:

Drum2001 wrote:
I have a report that acts as a time sheet for employees. It lists
there "punch-in" and "punch-out and catagorizes them by week, with
different totals..

At the end of each week is a total of all the hours for the week. The
pay week is from Monday -> Sunday, but with the query below, it is
totaling Sunday -> Saturday. Does anyone know how I could "shift" the
work week?

Any help would be great!!!!!!!!!

Thanks!

Brad

This query is TimeClockSum-Individual Week:

//
SELECT Sum(DateDiff("s ",[ClockIn],[ClockOut]))\3600 &
Format((Sum(Dat eDiff("s",[ClockIn],[ClockOut])) Mod
3600)/86400,":nn:ss") AS TotalTime,
DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1 AS WeekOf,
[ADateFilter - 1 Week].Employee, [ADateFilter - 1 Week].TimeTypeIn
FROM [ADateFilter - 1 Week]
GROUP BY DateAdd("d",-Weekday([ClockInDate]),[ClockInDate])+1,
[ADateFilter - 1 Week].Employee, [ADateFilter - 1 Week].TimeTypeIn
HAVING ((([ADateFilter - 1 Week].TimeTypeIn)="R egular"));
//
ADateFilter - 1 Week is the following query:

//
SELECT TimeClockHistor y.TimeEventID, TimeClockHistor y.Employee,
TimeClockHistor y.ClockInDate, TimeClockHistor y.ClockIn,
TimeClockHistor y.ReasonClockIn , TimeClockHistor y.TimeTypeIn,
TimeClockHistor y.ClockOutDate, TimeClockHistor y.ClockOut,
TimeClockHistor y.ReasonClockOu t, TimeClockHistor y.TimeTypeOut
FROM TimeClockHistor y
WHERE (((TimeClockHis tory.Employee)=[forms]![EmployeeFilter -
Admin]![cboEmployee]) AND ((TimeClockHist ory.ClockInDate ) Between
[forms]![EmployeeFilter - Admin]![Date1]+2 And [forms]![EmployeeFilter
- Admin]![Date1]-11));
//

Don't know if this helps, but WeekDay takes an option second argument
that specifies the first day of the week. Use vbSunday/1 for Sunday as
the start of the week, vbMonday/2 for Monday ect..

What would I put exatly and where. I replaced -Weekday with vbSunday/1
and recieved a syntax error. I then took out the "/1" and the query
ran, however, did not produce any results for this query now.

My comments are regarding the use of the Weekday function only, not
whether your SQL will return the correct records.

Did you not read VBA help on the Weekday function?
vbSunday is the default week start, so at the very least, you would
need vbMonday.
Also, in a query, you cannot use the VB constants (as you are not
using VB). Therefore, you would need to use the constant's value. For
vbMonday, that would be 2, i.e. WeekDay([YourFieldName],2)

Read the help files!
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail