Date Question

[Secret Squirrel]

I'm using "Date()-1" to filter one of my queries. How can I skip weekends
when using this? I need to show Friday's data on Monday's.

 

[Stefan Hoffmann]

hi,

I'm using "Date()-1" to filter one of my queries. How can I skip weekends
when using this? I need to show Friday's data on Monday's.

You can use the Weekday() function, e.g.

IIf(Weekday(Date()-1)=1, Date()-3, Date()-1)

Take a closer look at the OH, because the results of Weekday may vary.
So use the approbriate value for the start day of the week.


mfG
--> stefan <--

 

[John W. Vinson]

hi,


You can use the Weekday() function, e.g.

IIf(Weekday(Date()-1)=1, Date()-3, Date()-1)

Take a closer look at the OH, because the results of Weekday may vary.
So use the approbriate value for the start day of the week.


mfG
--> stefan <--

Squirrel, you may also need to allow for company holidays. What if Monday (or
Friday) wasn't a work day because it was a scheduled holiday?

You'll need a table of holidays - or, alternatively, a table of workdays out
into the future - and some code to check.

 

[Secret Squirrel]

Hi John,

I acutally have both of those tables in my DB. It might be easier to use the
table with the workdays, correct? How would I include that into my query?
Here's what my query looks like right now.

SELECT qryAttendanceDetails.EmployeeID, qryAttendanceDetails.DateAbsent,
[LastName] & ", " & [FirstName] AS EmpName, tblEmployees.ID,
tblDepartment.Department, tblEmployees.Dept, DeptFilter([Dept]) AS Result,
tblEmployees.Status, qryAttendanceDetails.Type, qryAttendanceDetails.TimeInOut
FROM tblDepartment INNER JOIN (tblEmployees INNER JOIN qryAttendanceDetails
ON tblEmployees.ID = qryAttendanceDetails.EmployeeID) ON tblDepartment.DeptID
= tblEmployees.Dept
GROUP BY qryAttendanceDetails.EmployeeID, qryAttendanceDetails.DateAbsent,
[LastName] & ", " & [FirstName], tblEmployees.ID, tblDepartment.Department,
tblEmployees.Dept, tblEmployees.Status, qryAttendanceDetails.Type,
qryAttendanceDetails.TimeInOut
HAVING
(((qryAttendanceDetails.DateAbsent)=IIf(Weekday(Date()-1)=1,Date()-3,Date()-1))
AND ((DeptFilter([Dept]))=[Forms]![frmHidden1]![txtDept]) AND
((tblEmployees.Status)="Active")) OR
(((qryAttendanceDetails.DateAbsent)=IIf(Weekday(Date()-1)=7,Date()-2,Date()-1))
AND ((DeptFilter([Dept]))=[Forms]![frmHidden1]![txtDept]) AND
((tblEmployees.Status)="Active")) OR
(((qryAttendanceDetails.DateAbsent)=IIf(Weekday(Date()-1)=6,Date()-1,Date()-1))
AND ((DeptFilter([Dept]))=[Forms]![frmHidden1]![txtDept]) AND
((tblEmployees.Status)="Active"));

Thanks
SS

 

[Secret Squirrel]

Hi John,

I acutally have both of those tables in my DB. It might be easier to use the
table with the workdays, correct? How would I include that into my query?
Here's what my query looks like right now.

SELECT qryAttendanceDetails.EmployeeID, qryAttendanceDetails.DateAbsent,
[LastName] & ", " & [FirstName] AS EmpName, tblEmployees.ID,
tblDepartment.Department, tblEmployees.Dept, DeptFilter([Dept]) AS Result,
tblEmployees.Status, qryAttendanceDetails.Type, qryAttendanceDetails.TimeInOut
FROM tblDepartment INNER JOIN (tblEmployees INNER JOIN qryAttendanceDetails
ON tblEmployees.ID = qryAttendanceDetails.EmployeeID) ON tblDepartment.DeptID
= tblEmployees.Dept
GROUP BY qryAttendanceDetails.EmployeeID, qryAttendanceDetails.DateAbsent,
[LastName] & ", " & [FirstName], tblEmployees.ID, tblDepartment.Department,
tblEmployees.Dept, tblEmployees.Status, qryAttendanceDetails.Type,
qryAttendanceDetails.TimeInOut
HAVING
(((qryAttendanceDetails.DateAbsent)=IIf(Weekday(Date()-1)=1,Date()-3,Date()-1))
AND ((DeptFilter([Dept]))=[Forms]![frmHidden1]![txtDept]) AND
((tblEmployees.Status)="Active")) OR
(((qryAttendanceDetails.DateAbsent)=IIf(Weekday(Date()-1)=7,Date()-2,Date()-1))
AND ((DeptFilter([Dept]))=[Forms]![frmHidden1]![txtDept]) AND
((tblEmployees.Status)="Active")) OR
(((qryAttendanceDetails.DateAbsent)=IIf(Weekday(Date()-1)=6,Date()-1,Date()-1))
AND ((DeptFilter([Dept]))=[Forms]![frmHidden1]![txtDept]) AND
((tblEmployees.Status)="Active"));

Thanks
SS