| re: Computing the past monday - Friday when Sunday occurs
rock72 wrote:
[color=blue]
> 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
>[/color]
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.
| 
