barkarlo

[barkarlo]

I need help to create a function that will return total worktime for each employee who works sundays.
I have the folloving simple table
ID Employee Datum Starttime Endtime
1 Patric Jon.. 12/04/2006 07:30 12:50
2 John Kol.. 13/04/2006 09:00 16:30
3 Patric Jon.. 14/04/2006 08:00 15:00

[Killer42]

I need help to create a function that will return total worktime for each employee who works sundays. I have the folloving simple table
ID Employee Datum Starttime Endtime
1 Patric Jon.. 12/04/2006 07:30 12:50
2 John Kol.. 13/04/2006 09:00 16:30
3 Patric Jon.. 14/04/2006 08:00 15:00

Could you show us what output you would expect to generate from this sample data?

[NeoPa]

Use standard arithmetic to extract period between two times.

SELECT [ID], [Employee], [Datum], Format([EndTime] - [StartTime], 'h:mm:ss') AS TimeWorked
FROM [Table]

[barkarlo]

Could you show us what output you would expect to generate from this sample data?

I know how much time Patric Jon works every day in a month, but I want to see subtotal timework only for sundays
if(for an example)12/04/06 and 14/04/06 are dates of sundays I would like to see in query
Employee Totalworktime
Patric Jon 13:20

[barkarlo]

Use standard arithmetic to extract period between two times.

SELECT [ID], [Employee], [Datum], Format([EndTime] - [StartTime], 'h:mm:ss') AS TimeWorked
FROM [Table]

I now this but I don't make subtotal timework for each employee.

[NeoPa]

OK I get that you know that (now you've explained it), but I still have very little idea what you're after. I don't want to spend loads of time guessing what you're trying to say.

[Killer42]

OK I get that you know that (now you've explained it), but I still have very little idea what you're after. I don't want to spend loads of time guessing what you're trying to say.

I think (this will be a good test of whether I understand the problem :)) that what barkalo wants is simply a subtotal which counts Sundays only. Or to put it another way, he wants to calculate the total hours worked per user (probably doing something like your post) but filtering out everything except Sunday.

Does this sound right, barkalo?

[MMcCarthy]

I need help to create a function that will return total worktime for each employee who works sundays.
I have the folloving simple table
ID Employee Datum Starttime Endtime
1 Patric Jon.. 12/04/2006 07:30 12:50
2 John Kol.. 13/04/2006 09:00 16:30
3 Patric Jon.. 14/04/2006 08:00 15:00

Add a field to your table called sundayHours. Make it a number datatype and format it as a double.

Then try this code:

 
Function SundayWorkHours(Starttime As Date, Endtime As Date) As Double
Dim hoursWorked As Double
Dim minsWorked As Integer
 
	minsWorked = DateDiff("n", Starttime, Endtime)
	hoursWorked = minsWorked / 60
	hoursWorked = Format(hoursWorked, "00.00")
	SundayWorkHours = hoursWorked
	
End Function
 
' as an example of what you put in your procedure
Sub temphours()
Dim db As Database
Dim rs As DAO.Recordset
 
	Set db = CurrentDb
	Set rs = db.OpenRecordset("SELECT ID, Employee, Datum, Starttime, Endtime, sundayHours FROM TableName;")
	rs.MoveFirst
	rs.FindFirst Weekday(rs!Datum, 1) = 1
	rs.Edit
	rs!sundayHours = SundayWorkHours(rs!Starttime, rs!Endtime)
	rs.Update
	Do Until rs.EOF Or rs.BOF
		' if the date is a sunday
		rs.FindNext Weekday(rs!Datum, 1) = 1
		rs.Edit
		rs!sundayHours = SundayWorkHours(rs!Starttime, rs!Endtime)
		rs.Update
	Loop
	rs.Close
	Set rs = Nothing
	Set db = Nothing
	
End Sub

[Russell G]

Hi

I may be over simplfing your problem but why not use the standard DatePart function where the interval is set to "w". IE DatePart ("w", «date», ) and the date being the start date of the period worked. The output of the above will be a 1 if the day was a Sunday and then sum the time differances where the above output = 1 and group by staff member. See help files in Access for more explanations.

Hope this helps.

[Killer42]

Hi
I may be over simplfing your problem but why not use the standard DatePart function where the interval is set to "w". IE DatePart ("w", «date», ) and the date being the start date of the period worked. The output of the above will be a 1 if the day was a Sunday and then sum the time differances where the above output = 1 and group by staff member. See help files in Access for more explanations.
Hope this helps.

One caveat, though. If I remember correctly, DatePart needs you to specify which weekday the week starts on. Or does it use your Windows settings? I forget.

Couldn't you use the Weekday() function, or am I mixing up my products? With VB/VBA in eveything, it's hard to keep them straight.

[barkarlo]

I think (this will be a good test of whether I understand the problem :)) that what barkalo wants is simply a subtotal which counts Sundays only. Or to put it another way, he wants to calculate the total hours worked per user (probably doing something like your post) but filtering out everything except Sunday.

Does this sound right, barkalo?

Yes,it sounds right.:)
Btw,
how to calculate the grandtotal of the workhours of all employees in the form, if I use
format([starttime]-1-[endtime],"short time")
in the query (because sometimes the end of the worktime passes midnight).

I tried with the function

Public Function HoursAndMinutes (interval as variant) as string
dim totalminutes as long, totalseconds as long
dim hours as long,minutes as long, seconds as long
if isnull(interval )=true then exit function
hours = int(CSng(interv al*24))
totalminutes = int(CSng(interv al*1440))
minutes = totalminutes mod 60
totalseconds = int(CSng(interv al*86400))
seconds = totalseconds mod 60
if seconds> 30 then minutes = minutes+1
if minutes> 59 then hours = hours+1:minutes = 0
HoursAndMinutes = hours & ":" & format(minutes, "00")
end function
which is OK until the end worktime doesn't pass midnight.
Than the function returns the result(-)
(for an example starttime 22:30 endtime 02:30 result -20
and I want the result for 4 hours

What to do to make the function return the correct time?

[barkarlo]

Add a field to your table called sundayHours. Make it a number datatype and format it as a double.

Then try this code:

 
Function SundayWorkHours(Starttime As Date, Endtime As Date) As Double
Dim hoursWorked As Double
Dim minsWorked As Integer
 
	minsWorked = DateDiff("n", Starttime, Endtime)
	hoursWorked = minsWorked / 60
	hoursWorked = Format(hoursWorked, "00.00")
	SundayWorkHours = hoursWorked
	
End Function
 
' as an example of what you put in your procedure
Sub temphours()
Dim db As Database
Dim rs As DAO.Recordset
 
	Set db = CurrentDb
	Set rs = db.OpenRecordset("SELECT ID, Employee, Datum, Starttime, Endtime, sundayHours FROM TableName;")
	rs.MoveFirst
	rs.FindFirst Weekday(rs!Datum, 1) = 1
	rs.Edit
	rs!sundayHours = SundayWorkHours(rs!Starttime, rs!Endtime)
	rs.Update
	Do Until rs.EOF Or rs.BOF
		' if the date is a sunday
		rs.FindNext Weekday(rs!Datum, 1) = 1
		rs.Edit
		rs!sundayHours = SundayWorkHours(rs!Starttime, rs!Endtime)
		rs.Update
	Loop
	rs.Close
	Set rs = Nothing
	Set db = Nothing
	
End Sub

Thanks for help

[barkarlo]

Hi

I may be over simplfing your problem but why not use the standard DatePart function where the interval is set to "w". IE DatePart ("w", «date», ) and the date being the start date of the period worked. The output of the above will be a 1 if the day was a Sunday and then sum the time differances where the above output = 1 and group by staff member. See help files in Access for more explanations.

Hope this helps.

Yes, thanks for help

[MMcCarthy]

Thanks for help

Did this work for you?