Computing the past monday - Friday when Sunday occurs

[rock72]

Hello Everyone,

Can you tell me how to produce a query that when Login Date (entry) is
Sunday. First, check if the Sunday Date occur on 7th day of his duty.

Example 1:

Login Date no. of wk days Remark

11/01/04 1
11/02/04 2
11/07/04 (Sunday) 3 - Regular Working Day
Example 2:

Login Date no. of wk days

11/01/04 1
11/02/04 2
11/03/04 3
11/04/04 4
11/07/04 (Sunday) 5 - Regular Working Day
11/08/04 1
11/09/04 2
11/10/04 3
11/11/04 4
11/12/04 5
11/13/04 6
11/14/04 (Sunday) 7 - Sunday Working Day

Take note of the Remark

This is the kind of query I am trying to produce for 2 months now.
But still I can't figure out what to do. Need Assistance ASAP
I want in this case if 11/07/04. Weekday is Sunday, right. If weekday
of login date is Sunday. Check the previous login date b4 11/07/04 if
no. of days from 11/01/04 to 11/07/04 is 7 days then 11/07/04 is
Sunday, if not equal to 7 working days then 11/07/04 is a regular
working day instead of Sunday Working day.

in this example: You can see that 11/07/04 login date falls on 3rd
working days. Therefore, 11/07/04 is not Sunday but as regular day.
I am refering to Working Day Status not the day of date.

Working Day Status is compose of three type:

Sunday Working Day - If the employee login date is Sunday and the
previous login date is not less than 6 days
before 11/07/04 for example.

Regular Working Day - If the employee login date is from Monday to
Saturday.

Holiday - If the employee login date is declared as
Holiday like: 12/25/04 - Christmas Eve.

How will I do this ? I need detailed solution. the 7 days computation
will occur everytime the login date's weekday is vbSunday.

So, It is a continous entry regardless of month and weeks. As long as
the 7th day is Sunday, consider it as Sunday Working Day. Otherwise, if
Sunday falls less than 7 days then it is considered as Regulard Working
Day instead of Sunday Working Day.

I hope you got the point. I tried this several times but no one can
give me the exact solution. All the solution given to me is good only
for 1st week but when 2nd week or shall I say 2nd Sunday occurs, the
result is not the way it should be. Maybe the solution did not compute
7 days again for the succeeding weeks.

As long as Sunday Occurs in login date, I want a solution that it
computes where the login date falls. See above illustration and read
carefully to follow what I'm trying to accomplish in this application.
Thanks

Tim

[Bruce Dodds]

rock72 wrote:

Hello Everyone,

Can you tell me how to produce a query that when Login Date (entry) is
Sunday. First, check if the Sunday Date occur on 7th day of his duty.

Example 1:

Login Date no. of wk days Remark

11/01/04 1
11/02/04 2
11/07/04 (Sunday) 3 - Regular Working Day
Example 2:

Login Date no. of wk days

11/01/04 1
11/02/04 2
11/03/04 3
11/04/04 4
11/07/04 (Sunday) 5 - Regular Working Day
11/08/04 1
11/09/04 2
11/10/04 3
11/11/04 4
11/12/04 5
11/13/04 6
11/14/04 (Sunday) 7 - Sunday Working Day

Take note of the Remark

This is the kind of query I am trying to produce for 2 months now.
But still I can't figure out what to do. Need Assistance ASAP
I want in this case if 11/07/04. Weekday is Sunday, right. If weekday
of login date is Sunday. Check the previous login date b4 11/07/04 if
no. of days from 11/01/04 to 11/07/04 is 7 days then 11/07/04 is
Sunday, if not equal to 7 working days then 11/07/04 is a regular
working day instead of Sunday Working day.

in this example: You can see that 11/07/04 login date falls on 3rd
working days. Therefore, 11/07/04 is not Sunday but as regular day.
I am refering to Working Day Status not the day of date.

Working Day Status is compose of three type:

Sunday Working Day - If the employee login date is Sunday and the
previous login date is not less than 6 days
before 11/07/04 for example.

Regular Working Day - If the employee login date is from Monday to
Saturday.

Holiday - If the employee login date is declared as
Holiday like: 12/25/04 - Christmas Eve.

How will I do this ? I need detailed solution. the 7 days computation
will occur everytime the login date's weekday is vbSunday.

So, It is a continous entry regardless of month and weeks. As long as
the 7th day is Sunday, consider it as Sunday Working Day. Otherwise, if
Sunday falls less than 7 days then it is considered as Regulard Working
Day instead of Sunday Working Day.

I hope you got the point. I tried this several times but no one can
give me the exact solution. All the solution given to me is good only
for 1st week but when 2nd week or shall I say 2nd Sunday occurs, the
result is not the way it should be. Maybe the solution did not compute
7 days again for the succeeding weeks.

As long as Sunday Occurs in login date, I want a solution that it
computes where the login date falls. See above illustration and read
carefully to follow what I'm trying to accomplish in this application.
Thanks

Tim

Tim:

I tested a solution. I set up a tblLogin containing as UserID (integer),
LoginDate (Date), and Worktype ("G", "S" or "H" for type of workday. I I
add records for User #1 for the dates 11/8/04 through 11/13/04.

I created the following query:

PARAMETERS UserID Short, TimeClock DateTime;
INSERT INTO tblLogin ( UserID, LoginDate, WorkType )
SELECT [UserID] AS [User], [TimeClock] AS LD,
GetWorkType([UserID],[TimeClock]) AS WT;

The query calls this routine:

'(START OF CODE)

Public Function GetWorkType(intUserID As Integer, dteLoginDate As Date)
As String
Dim varDayOfWeek As Variant
Dim intDaysWorked As Integer
Dim strCriteria As String

varDayOfWeek = Weekday(dteLoginDate)
If varDayOfWeek <> vbSunday Then
GetWorkType = "R"
Else
strCriteria = "[UserID] = " & intUserID & " AND ("
strCriteria = strCriteria & "[LoginDate] >= #" & DateAdd("d",
-6, dteLoginDate) & "#"
strCriteria = strCriteria & " AND [LoginDate] < #" &
dteLoginDate & "#)"
intDaysWorked = DCount("UserID", "tblLogin", strCriteria)
If intDaysWorked < 6 Then
GetWorkType = "R"
Else
GetWorkType = "S"
End If
End If

End Function

'(END OF CODE)

I then ran the query, entering 1 for the UserID parameter and 11/14/04
for the LoginDate. It returned "S" as the WorkType, which was correct.

You will have to modify this for your own application, of course, but I
hope this gives you an approach to solving the problem.