Work hours by Ken Sheridan.

K

Keith

Can someone please help with the following function that was provided by Ken
Sheridan. I pasted this in the VBA but I guess I need to replace some of the
field names with the names of my fields correct? I keep getting an undefined
function 'worktime' so maybe I pasted it incorrectly in Visual Basic?
Thanks for any help,
Keith


To get the actual time worked you could call a VBA function. The following
is adapted from one of mine for computing elapsed times and should give you
the correct duration in the format hh:nn:ss subject to a few assumptions:

1. That the start and end date/time values can only be on a weekday.
2. That a lunch break of fixed length is taken each day.
3. That lunch breaks are taken on every day, including the first and last.

Public Function WorkTime(dtmStart As Date, _
dtmEnd As Date, _
dtmTimeIn As Date, _
dtmTimeOut As Date, _
intLunchMinutes As Integer) As String

Const HOURSINDAY = 24
Const MINUTESINDAY = 1440

Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
Dim dtmDay As Date

For dtmDay = DateValue(dtmStart) To DateValue(dtmEnd)
' if weekend do nothing
If Weekday(dtmDay, vbMonday) < 6 Then
Select Case dtmDay
Case Is = DateValue(dtmStart) ' first day
' get time from start until end of work day
dblDuration = dblDuration + (dtmTimeOut -
TimeValue(dtmStart))
' subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
' if first and last day are same day then subtract
' time from time out to end of time worked
If dtmDay = DateValue(dtmEnd) Then
dblDuration = dblDuration - (dtmTimeOut -
TimeValue(dtmEnd))
End If
Case Is = DateValue(dtmEnd) ' last day
' get time from start of work day until end time
dblDuration = dblDuration + (TimeValue(dtmEnd) -
dtmTimeIn)
' if start time after lunch subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
Case Else
' if last day is day after first day do nothing more
' otherwise compute time worked for a full day
If DateValue(dtmEnd) - DateValue(dtmStart) > 1 Then
dblDuration = dblDuration + (dtmTimeOut - dtmTimeIn
- (intLunchMinutes / MINUTESINDAY))
End If
End Select
End If
Next dtmDay

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

WorkTime = lngHours & strMinutesSeconds

End Function

You'll probably find that some longer lines in the code have been split over
two lines in your newsreader, so you might need to correct this after pasting
the function into a standard module in your database.

Assuming a day from 8:00 AM to 6:00PM with a 60 minute lunch break you'd
call the function in a query like so:

SELECT event, user, startdate, enddate,
WorkTime(startdate, enddate, #08:00#, #18:00#, 60)
AS workedtime
FROM YourTable;

or you could call it in the same way in the ControlSource of a computed
control in a form or report.

Ken Sheridan
Stafford, England

AleJeSe said:
Given the following table tblEvents,

event user startdate enddate
elapsed work
0156 Pam 10/02/200710:15 20/02/2007 15:44 10d 5h 29m ???????

I can´t figure out a query to update multiple records with real work times
between these two points, in minutes/hours, excluding nonworking times (sat,
sun, 18:00/8:00, lunch...)

I've been trying with some subqueries but I'm a newbie with Access

Do you have any example that will do to adjust?

Thanks in advance


Alex
Click to expand...

Was this post helpful to you?

1 out of 1 people found this post helpful.
 
D

Duane Hookom

Did you name the module the same as the function? If so, change it to
"modDateTimeCalcs". Then find and use a naming convention that doesn't allow
duplication like this.
 
K

Keith

Thanks Duane,
I changed the module name to modDateTimeCalcs but I don't understand the
second part of your statement "Then find and use a naming convention that
doesn't allow duplication like this." I'm still getting the same error so I
think I just need this second part...sorry I'm not getting it.

Now this is a Class module correct?
 
D

Duane Hookom

My guess was that you had named the function and the module the same. This
would be one possible cause of your symptom. I recommend using a naming
convention so that module names begin with either "bas" or "mod". My form
names generally begin with "frm" and reports "rpt". The use of this
"standard" causes much debate but it provides some much needed consistency in
my programming life.

If changing the name of the module didn't fix the problem then you might
search all of your VBA to see if you have two or more copies of the same
function name.
 
K

Keith

Argh...I looked through the VB and don't see anything else.

Do I need to change some of the strings to my field names?
Like this line "For dtmDay = DateValue(dtmStart) To DateValue(dtmEnd)"
should DateValue (dtmStart) be my date field?
 
K

Keith

Library states "order"
Class "moddatetimecalcs"
member "worktime"

is this correct?
___________________________
 
D

Duane Hookom

You pass your field "values" to the function. The variable names used in the
code are "declared" in the arguments of the function.

Maybe you need to re-post your exact error message and your code.
 
K

Keith

Thanks Ken for spending so much time with this...

Maybe I'm calling it wrong.
In my query, it's:
SELECT Sales Support Specialist], [Assigned to SS], [SS Rejected to Sales],
worktime([assigned to ss],[ss rejected to sales],#2/01/2008 8:0:0#,#2/29/2008
18:0:0#,60) AS Workedtime
FROM order_log
WHERE (((order_log.[SS Rejected to Sales]) Is Not Null));


Error message "undefined expression 'worktime' in expression.


Public Function WorkTime(dtmStart As Date, _
dtmEnd As Date, _
dtmTimeIn As Date, _
dtmTimeOut As Date, _
intLunchMinutes As Integer) As String

Const HOURSINDAY = 24
Const MINUTESINDAY = 1440

Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
Dim dtmDay As Date

For dtmDay = DateValue(dtmStart) To DateValue(dtmEnd)
' if weekend do nothing
If Weekday(dtmDay, vbMonday) < 6 Then
Select Case dtmDay
Case Is = DateValue(dtmStart) ' first day
' get time from start until end of work day
dblDuration = dblDuration + (dtmTimeOut -
TimeValue(dtmStart))
' subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
' if first and last day are same day then subtract
' time from time out to end of time worked
If dtmDay = DateValue(dtmEnd) Then
dblDuration = dblDuration - (dtmTimeOut -
TimeValue(dtmEnd))
End If
Case Is = DateValue(dtmEnd) ' last day
' get time from start of work day until end time
dblDuration = dblDuration + (TimeValue(dtmEnd) -
dtmTimeIn)
' if start time after lunch subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
Case Else
' if last day is day after first day do nothing more
' otherwise compute time worked for a full day
If DateValue(dtmEnd) - DateValue(dtmStart) > 1 Then
dblDuration = dblDuration + (dtmTimeOut - dtmTimeIn
- (intLunchMinutes / MINUTESINDAY))
End If
End Select
End If
Next dtmDay

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

WorkTime = lngHours & strMinutesSeconds

End Function
 
D

Duane Hookom

Apparently you understand how to pass values (field or other) to the function.

Is your query running in Access?
Your SQL syntax has issues. I can't see how it would return anything. There
is at least one bracket missing and I don't think the dtmTime... values are
correct.
--
Duane Hookom
Microsoft Access MVP


Keith said:
Thanks Ken for spending so much time with this...

Maybe I'm calling it wrong.
In my query, it's:
SELECT Sales Support Specialist], [Assigned to SS], [SS Rejected to Sales],
worktime([assigned to ss],[ss rejected to sales],#2/01/2008 8:0:0#,#2/29/2008
18:0:0#,60) AS Workedtime
FROM order_log
WHERE (((order_log.[SS Rejected to Sales]) Is Not Null));


Error message "undefined expression 'worktime' in expression.


Public Function WorkTime(dtmStart As Date, _
dtmEnd As Date, _
dtmTimeIn As Date, _
dtmTimeOut As Date, _
intLunchMinutes As Integer) As String

Const HOURSINDAY = 24
Const MINUTESINDAY = 1440

Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
Dim dtmDay As Date

For dtmDay = DateValue(dtmStart) To DateValue(dtmEnd)
' if weekend do nothing
If Weekday(dtmDay, vbMonday) < 6 Then
Select Case dtmDay
Case Is = DateValue(dtmStart) ' first day
' get time from start until end of work day
dblDuration = dblDuration + (dtmTimeOut -
TimeValue(dtmStart))
' subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
' if first and last day are same day then subtract
' time from time out to end of time worked
If dtmDay = DateValue(dtmEnd) Then
dblDuration = dblDuration - (dtmTimeOut -
TimeValue(dtmEnd))
End If
Case Is = DateValue(dtmEnd) ' last day
' get time from start of work day until end time
dblDuration = dblDuration + (TimeValue(dtmEnd) -
dtmTimeIn)
' if start time after lunch subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
Case Else
' if last day is day after first day do nothing more
' otherwise compute time worked for a full day
If DateValue(dtmEnd) - DateValue(dtmStart) > 1 Then
dblDuration = dblDuration + (dtmTimeOut - dtmTimeIn
- (intLunchMinutes / MINUTESINDAY))
End If
End Select
End If
Next dtmDay

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

WorkTime = lngHours & strMinutesSeconds

End Function




Duane Hookom said:
I'm not sure what all this means :-(
Click to expand...
Click to expand...
 
K

Keith

I don't know if I do. I don't understand how the function knows to replace my
fields [assigned to ss] and [ss rejected to sales] in place of the [dtmstart]
and [dtmend].


Yes, the query is running in Access and I do have a bracket missing but it's
still getting the same error.

Maybe I need to scrap this and think of another way to get my results. I'm
just trying to figure out how much time was spent on a task in working
minutes. It seems like it should be so much easier than this...



Duane Hookom said:
Apparently you understand how to pass values (field or other) to the function.

Is your query running in Access?
Your SQL syntax has issues. I can't see how it would return anything. There
is at least one bracket missing and I don't think the dtmTime... values are
correct.
--
Duane Hookom
Microsoft Access MVP


Keith said:
Thanks Ken for spending so much time with this...

Maybe I'm calling it wrong.
In my query, it's:
SELECT Sales Support Specialist], [Assigned to SS], [SS Rejected to Sales],
worktime([assigned to ss],[ss rejected to sales],#2/01/2008 8:0:0#,#2/29/2008
18:0:0#,60) AS Workedtime
FROM order_log
WHERE (((order_log.[SS Rejected to Sales]) Is Not Null));


Error message "undefined expression 'worktime' in expression.


Public Function WorkTime(dtmStart As Date, _
dtmEnd As Date, _
dtmTimeIn As Date, _
dtmTimeOut As Date, _
intLunchMinutes As Integer) As String

Const HOURSINDAY = 24
Const MINUTESINDAY = 1440

Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
Dim dtmDay As Date

For dtmDay = DateValue(dtmStart) To DateValue(dtmEnd)
' if weekend do nothing
If Weekday(dtmDay, vbMonday) < 6 Then
Select Case dtmDay
Case Is = DateValue(dtmStart) ' first day
' get time from start until end of work day
dblDuration = dblDuration + (dtmTimeOut -
TimeValue(dtmStart))
' subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
' if first and last day are same day then subtract
' time from time out to end of time worked
If dtmDay = DateValue(dtmEnd) Then
dblDuration = dblDuration - (dtmTimeOut -
TimeValue(dtmEnd))
End If
Case Is = DateValue(dtmEnd) ' last day
' get time from start of work day until end time
dblDuration = dblDuration + (TimeValue(dtmEnd) -
dtmTimeIn)
' if start time after lunch subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
Case Else
' if last day is day after first day do nothing more
' otherwise compute time worked for a full day
If DateValue(dtmEnd) - DateValue(dtmStart) > 1 Then
dblDuration = dblDuration + (dtmTimeOut - dtmTimeIn
- (intLunchMinutes / MINUTESINDAY))
End If
End Select
End If
Next dtmDay

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

WorkTime = lngHours & strMinutesSeconds

End Function




Duane Hookom said:
I'm not sure what all this means :-(
--
Duane Hookom
Microsoft Access MVP


:

Library states "order"
Class "moddatetimecalcs"
member "worktime"

is this correct?
___________________________

:

My guess was that you had named the function and the module the same. This
would be one possible cause of your symptom. I recommend using a naming
convention so that module names begin with either "bas" or "mod". My form
names generally begin with "frm" and reports "rpt". The use of this
"standard" causes much debate but it provides some much needed consistency in
my programming life.

If changing the name of the module didn't fix the problem then you might
search all of your VBA to see if you have two or more copies of the same
function name.
--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane,
I changed the module name to modDateTimeCalcs but I don't understand the
second part of your statement "Then find and use a naming convention that
doesn't allow duplication like this." I'm still getting the same error so I
think I just need this second part...sorry I'm not getting it.

Now this is a Class module correct?


:

Did you name the module the same as the function? If so, change it to
"modDateTimeCalcs". Then find and use a naming convention that doesn't allow
duplication like this.

--
Duane Hookom
Microsoft Access MVP


:

Can someone please help with the following function that was provided by Ken
Sheridan. I pasted this in the VBA but I guess I need to replace some of the
field names with the names of my fields correct? I keep getting an undefined
function 'worktime' so maybe I pasted it incorrectly in Visual Basic?
Thanks for any help,
Keith


To get the actual time worked you could call a VBA function. The following
is adapted from one of mine for computing elapsed times and should give you
the correct duration in the format hh:nn:ss subject to a few assumptions:

1. That the start and end date/time values can only be on a weekday.
2. That a lunch break of fixed length is taken each day.
3. That lunch breaks are taken on every day, including the first and last.

Public Function WorkTime(dtmStart As Date, _
dtmEnd As Date, _
dtmTimeIn As Date, _
dtmTimeOut As Date, _
intLunchMinutes As Integer) As String

Const HOURSINDAY = 24
Const MINUTESINDAY = 1440

Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
Dim dtmDay As Date

For dtmDay = DateValue(dtmStart) To DateValue(dtmEnd)
' if weekend do nothing
If Weekday(dtmDay, vbMonday) < 6 Then
Select Case dtmDay
Case Is = DateValue(dtmStart) ' first day
' get time from start until end of work day
dblDuration = dblDuration + (dtmTimeOut -
TimeValue(dtmStart))
' subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
' if first and last day are same day then subtract
' time from time out to end of time worked
If dtmDay = DateValue(dtmEnd) Then
dblDuration = dblDuration - (dtmTimeOut -
TimeValue(dtmEnd))
End If
Case Is = DateValue(dtmEnd) ' last day
' get time from start of work day until end time
dblDuration = dblDuration + (TimeValue(dtmEnd) -
dtmTimeIn)
' if start time after lunch subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
Case Else
' if last day is day after first day do nothing more
' otherwise compute time worked for a full day
If DateValue(dtmEnd) - DateValue(dtmStart) > 1 Then
dblDuration = dblDuration + (dtmTimeOut - dtmTimeIn
- (intLunchMinutes / MINUTESINDAY))
End If
End Select
End If
Next dtmDay

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

WorkTime = lngHours & strMinutesSeconds

End Function

You'll probably find that some longer lines in the code have been split over
two lines in your newsreader, so you might need to correct this after pasting
the function into a standard module in your database.

Assuming a day from 8:00 AM to 6:00PM with a 60 minute lunch break you'd
call the function in a query like so:

SELECT event, user, startdate, enddate,
WorkTime(startdate, enddate, #08:00#, #18:00#, 60)
AS workedtime
FROM YourTable;

or you could call it in the same way in the ControlSource of a computed
control in a form or report.

Ken Sheridan
Stafford, England

:

Given the following table tblEvents,

event user startdate enddate
elapsed work
0156 Pam 10/02/200710:15 20/02/2007 15:44 10d 5h 29m ???????

I can´t figure out a query to update multiple records with real work times
between these two points, in minutes/hours, excluding nonworking times (sat,
sun, 18:00/8:00, lunch...)

I've been trying with some subqueries but I'm a newbie with Access

Do you have any example that will do to adjust?

Thanks in advance


Alex



Was this post helpful to you?

1 out of 1 people found this post helpful.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

A user-defined function is nearly the same as any other Access/VBA function.
Assuming a function like Mid(), the developer provides 2 or 3 arguments like
Mid([MyTextValue], 4,6)
You have done the same with the WorkTime() function. I would expect the
appropriate syntax would be:
worktime([assigned to ss],[ss rejected to sales],#8:00:00#,#18:00:00#,60)
AS Workedtime

Did you add the function to a form or report module? Have you attempted to
compile your application? If you open any module and search for "Function
Worktime", where do you find it?
--
Duane Hookom
Microsoft Access MVP


Keith said:
I don't know if I do. I don't understand how the function knows to replace my
fields [assigned to ss] and [ss rejected to sales] in place of the [dtmstart]
and [dtmend].


Yes, the query is running in Access and I do have a bracket missing but it's
still getting the same error.

Maybe I need to scrap this and think of another way to get my results. I'm
just trying to figure out how much time was spent on a task in working
minutes. It seems like it should be so much easier than this...



Duane Hookom said:
Apparently you understand how to pass values (field or other) to the function.

Is your query running in Access?
Your SQL syntax has issues. I can't see how it would return anything. There
is at least one bracket missing and I don't think the dtmTime... values are
correct.
--
Duane Hookom
Microsoft Access MVP


Keith said:
Thanks Ken for spending so much time with this...

Maybe I'm calling it wrong.
In my query, it's:
SELECT Sales Support Specialist], [Assigned to SS], [SS Rejected to Sales],
worktime([assigned to ss],[ss rejected to sales],#2/01/2008 8:0:0#,#2/29/2008
18:0:0#,60) AS Workedtime
FROM order_log
WHERE (((order_log.[SS Rejected to Sales]) Is Not Null));


Error message "undefined expression 'worktime' in expression.


Public Function WorkTime(dtmStart As Date, _
dtmEnd As Date, _
dtmTimeIn As Date, _
dtmTimeOut As Date, _
intLunchMinutes As Integer) As String

Const HOURSINDAY = 24
Const MINUTESINDAY = 1440

Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
Dim dtmDay As Date

For dtmDay = DateValue(dtmStart) To DateValue(dtmEnd)
' if weekend do nothing
If Weekday(dtmDay, vbMonday) < 6 Then
Select Case dtmDay
Case Is = DateValue(dtmStart) ' first day
' get time from start until end of work day
dblDuration = dblDuration + (dtmTimeOut -
TimeValue(dtmStart))
' subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
' if first and last day are same day then subtract
' time from time out to end of time worked
If dtmDay = DateValue(dtmEnd) Then
dblDuration = dblDuration - (dtmTimeOut -
TimeValue(dtmEnd))
End If
Case Is = DateValue(dtmEnd) ' last day
' get time from start of work day until end time
dblDuration = dblDuration + (TimeValue(dtmEnd) -
dtmTimeIn)
' if start time after lunch subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
Case Else
' if last day is day after first day do nothing more
' otherwise compute time worked for a full day
If DateValue(dtmEnd) - DateValue(dtmStart) > 1 Then
dblDuration = dblDuration + (dtmTimeOut - dtmTimeIn
- (intLunchMinutes / MINUTESINDAY))
End If
End Select
End If
Next dtmDay

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

WorkTime = lngHours & strMinutesSeconds

End Function




:

I'm not sure what all this means :-(
--
Duane Hookom
Microsoft Access MVP


:

Library states "order"
Class "moddatetimecalcs"
member "worktime"

is this correct?
___________________________

:

My guess was that you had named the function and the module the same. This
would be one possible cause of your symptom. I recommend using a naming
convention so that module names begin with either "bas" or "mod". My form
names generally begin with "frm" and reports "rpt". The use of this
"standard" causes much debate but it provides some much needed consistency in
my programming life.

If changing the name of the module didn't fix the problem then you might
search all of your VBA to see if you have two or more copies of the same
function name.
--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane,
I changed the module name to modDateTimeCalcs but I don't understand the
second part of your statement "Then find and use a naming convention that
doesn't allow duplication like this." I'm still getting the same error so I
think I just need this second part...sorry I'm not getting it.

Now this is a Class module correct?


:

Did you name the module the same as the function? If so, change it to
"modDateTimeCalcs". Then find and use a naming convention that doesn't allow
duplication like this.

--
Duane Hookom
Microsoft Access MVP


:

Can someone please help with the following function that was provided by Ken
Sheridan. I pasted this in the VBA but I guess I need to replace some of the
field names with the names of my fields correct? I keep getting an undefined
function 'worktime' so maybe I pasted it incorrectly in Visual Basic?
Thanks for any help,
Keith


To get the actual time worked you could call a VBA function. The following
is adapted from one of mine for computing elapsed times and should give you
the correct duration in the format hh:nn:ss subject to a few assumptions:

1. That the start and end date/time values can only be on a weekday.
2. That a lunch break of fixed length is taken each day.
3. That lunch breaks are taken on every day, including the first and last.

Public Function WorkTime(dtmStart As Date, _
dtmEnd As Date, _
dtmTimeIn As Date, _
dtmTimeOut As Date, _
intLunchMinutes As Integer) As String

Const HOURSINDAY = 24
Const MINUTESINDAY = 1440

Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
Dim dtmDay As Date

For dtmDay = DateValue(dtmStart) To DateValue(dtmEnd)
' if weekend do nothing
If Weekday(dtmDay, vbMonday) < 6 Then
Select Case dtmDay
Case Is = DateValue(dtmStart) ' first day
' get time from start until end of work day
dblDuration = dblDuration + (dtmTimeOut -
TimeValue(dtmStart))
' subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
' if first and last day are same day then subtract
' time from time out to end of time worked
If dtmDay = DateValue(dtmEnd) Then
dblDuration = dblDuration - (dtmTimeOut -
TimeValue(dtmEnd))
End If
Case Is = DateValue(dtmEnd) ' last day
' get time from start of work day until end time
dblDuration = dblDuration + (TimeValue(dtmEnd) -
dtmTimeIn)
' if start time after lunch subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
Case Else
' if last day is day after first day do nothing more
' otherwise compute time worked for a full day
If DateValue(dtmEnd) - DateValue(dtmStart) > 1 Then
dblDuration = dblDuration + (dtmTimeOut - dtmTimeIn
- (intLunchMinutes / MINUTESINDAY))
End If
End Select
End If
Next dtmDay

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

WorkTime = lngHours & strMinutesSeconds

End Function

You'll probably find that some longer lines in the code have been split over
two lines in your newsreader, so you might need to correct this after pasting
the function into a standard module in your database.

Assuming a day from 8:00 AM to 6:00PM with a 60 minute lunch break you'd
call the function in a query like so:

SELECT event, user, startdate, enddate,
WorkTime(startdate, enddate, #08:00#, #18:00#, 60)
AS workedtime
FROM YourTable;

or you could call it in the same way in the ControlSource of a computed
control in a form or report.

Ken Sheridan
Stafford, England

:

Given the following table tblEvents,

event user startdate enddate
elapsed work
0156 Pam 10/02/200710:15 20/02/2007 15:44 10d 5h 29m ???????

I can´t figure out a query to update multiple records with real work times
between these two points, in minutes/hours, excluding nonworking times (sat,
sun, 18:00/8:00, lunch...)

I've been trying with some subqueries but I'm a newbie with Access

Do you have any example that will do to adjust?

Thanks in advance


Alex



Was this post helpful to you?

1 out of 1 people found this post helpful.
Click to expand...
Click to expand...
Click to expand...
 
K

Keith

When I go to design, I added it to the modules (the last one in the list ie.
Tables, Forms, Reports, Pages, Macros, Modules). When I do a search it's in
the class module. Yes, I compiled it but after that I can't compile it again,
it won't give me the option.

Any other suggestions? Thanks again for your help.


Duane Hookom said:
A user-defined function is nearly the same as any other Access/VBA function.
Assuming a function like Mid(), the developer provides 2 or 3 arguments like
Mid([MyTextValue], 4,6)
You have done the same with the WorkTime() function. I would expect the
appropriate syntax would be:
worktime([assigned to ss],[ss rejected to sales],#8:00:00#,#18:00:00#,60)
AS Workedtime

Did you add the function to a form or report module? Have you attempted to
compile your application? If you open any module and search for "Function
Worktime", where do you find it?
--
Duane Hookom
Microsoft Access MVP


Keith said:
I don't know if I do. I don't understand how the function knows to replace my
fields [assigned to ss] and [ss rejected to sales] in place of the [dtmstart]
and [dtmend].


Yes, the query is running in Access and I do have a bracket missing but it's
still getting the same error.

Maybe I need to scrap this and think of another way to get my results. I'm
just trying to figure out how much time was spent on a task in working
minutes. It seems like it should be so much easier than this...



Duane Hookom said:
Apparently you understand how to pass values (field or other) to the function.

Is your query running in Access?
Your SQL syntax has issues. I can't see how it would return anything. There
is at least one bracket missing and I don't think the dtmTime... values are
correct.
--
Duane Hookom
Microsoft Access MVP


:

Thanks Ken for spending so much time with this...

Maybe I'm calling it wrong.
In my query, it's:
SELECT Sales Support Specialist], [Assigned to SS], [SS Rejected to Sales],
worktime([assigned to ss],[ss rejected to sales],#2/01/2008 8:0:0#,#2/29/2008
18:0:0#,60) AS Workedtime
FROM order_log
WHERE (((order_log.[SS Rejected to Sales]) Is Not Null));


Error message "undefined expression 'worktime' in expression.


Public Function WorkTime(dtmStart As Date, _
dtmEnd As Date, _
dtmTimeIn As Date, _
dtmTimeOut As Date, _
intLunchMinutes As Integer) As String

Const HOURSINDAY = 24
Const MINUTESINDAY = 1440

Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
Dim dtmDay As Date

For dtmDay = DateValue(dtmStart) To DateValue(dtmEnd)
' if weekend do nothing
If Weekday(dtmDay, vbMonday) < 6 Then
Select Case dtmDay
Case Is = DateValue(dtmStart) ' first day
' get time from start until end of work day
dblDuration = dblDuration + (dtmTimeOut -
TimeValue(dtmStart))
' subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
' if first and last day are same day then subtract
' time from time out to end of time worked
If dtmDay = DateValue(dtmEnd) Then
dblDuration = dblDuration - (dtmTimeOut -
TimeValue(dtmEnd))
End If
Case Is = DateValue(dtmEnd) ' last day
' get time from start of work day until end time
dblDuration = dblDuration + (TimeValue(dtmEnd) -
dtmTimeIn)
' if start time after lunch subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
Case Else
' if last day is day after first day do nothing more
' otherwise compute time worked for a full day
If DateValue(dtmEnd) - DateValue(dtmStart) > 1 Then
dblDuration = dblDuration + (dtmTimeOut - dtmTimeIn
- (intLunchMinutes / MINUTESINDAY))
End If
End Select
End If
Next dtmDay

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

WorkTime = lngHours & strMinutesSeconds

End Function




:

I'm not sure what all this means :-(
--
Duane Hookom
Microsoft Access MVP


:

Library states "order"
Class "moddatetimecalcs"
member "worktime"

is this correct?
___________________________

:

My guess was that you had named the function and the module the same. This
would be one possible cause of your symptom. I recommend using a naming
convention so that module names begin with either "bas" or "mod". My form
names generally begin with "frm" and reports "rpt". The use of this
"standard" causes much debate but it provides some much needed consistency in
my programming life.

If changing the name of the module didn't fix the problem then you might
search all of your VBA to see if you have two or more copies of the same
function name.
--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane,
I changed the module name to modDateTimeCalcs but I don't understand the
second part of your statement "Then find and use a naming convention that
doesn't allow duplication like this." I'm still getting the same error so I
think I just need this second part...sorry I'm not getting it.

Now this is a Class module correct?


:

Did you name the module the same as the function? If so, change it to
"modDateTimeCalcs". Then find and use a naming convention that doesn't allow
duplication like this.

--
Duane Hookom
Microsoft Access MVP


:

Can someone please help with the following function that was provided by Ken
Sheridan. I pasted this in the VBA but I guess I need to replace some of the
field names with the names of my fields correct? I keep getting an undefined
function 'worktime' so maybe I pasted it incorrectly in Visual Basic?
Thanks for any help,
Keith


To get the actual time worked you could call a VBA function. The following
is adapted from one of mine for computing elapsed times and should give you
the correct duration in the format hh:nn:ss subject to a few assumptions:

1. That the start and end date/time values can only be on a weekday.
2. That a lunch break of fixed length is taken each day.
3. That lunch breaks are taken on every day, including the first and last.

Public Function WorkTime(dtmStart As Date, _
dtmEnd As Date, _
dtmTimeIn As Date, _
dtmTimeOut As Date, _
intLunchMinutes As Integer) As String

Const HOURSINDAY = 24
Const MINUTESINDAY = 1440

Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
Dim dtmDay As Date

For dtmDay = DateValue(dtmStart) To DateValue(dtmEnd)
' if weekend do nothing
If Weekday(dtmDay, vbMonday) < 6 Then
Select Case dtmDay
Case Is = DateValue(dtmStart) ' first day
' get time from start until end of work day
dblDuration = dblDuration + (dtmTimeOut -
TimeValue(dtmStart))
' subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
' if first and last day are same day then subtract
' time from time out to end of time worked
If dtmDay = DateValue(dtmEnd) Then
dblDuration = dblDuration - (dtmTimeOut -
TimeValue(dtmEnd))
End If
Case Is = DateValue(dtmEnd) ' last day
' get time from start of work day until end time
dblDuration = dblDuration + (TimeValue(dtmEnd) -
dtmTimeIn)
' if start time after lunch subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
Case Else
' if last day is day after first day do nothing more
' otherwise compute time worked for a full day
If DateValue(dtmEnd) - DateValue(dtmStart) > 1 Then
dblDuration = dblDuration + (dtmTimeOut - dtmTimeIn
- (intLunchMinutes / MINUTESINDAY))
End If
End Select
End If
Next dtmDay

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

WorkTime = lngHours & strMinutesSeconds

End Function

You'll probably find that some longer lines in the code have been split over
two lines in your newsreader, so you might need to correct this after pasting
the function into a standard module in your database.

Assuming a day from 8:00 AM to 6:00PM with a 60 minute lunch break you'd
call the function in a query like so:

SELECT event, user, startdate, enddate,
WorkTime(startdate, enddate, #08:00#, #18:00#, 60)
AS workedtime
FROM YourTable;

or you could call it in the same way in the ControlSource of a computed
control in a form or report.

Ken Sheridan
Stafford, England

:

Given the following table tblEvents,

event user startdate enddate
elapsed work
0156 Pam 10/02/200710:15 20/02/2007 15:44 10d 5h 29m ???????

I can´t figure out a query to update multiple records with real work times
between these two points, in minutes/hours, excluding nonworking times (sat,
sun, 18:00/8:00, lunch...)

I've been trying with some subqueries but I'm a newbie with Access

Do you have any example that will do to adjust?

Thanks in advance


Alex
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Is there some reason you created a "class" module rather than a standard
module? Try delete the function from the class module and paste it into a
standard module.
--
Duane Hookom
Microsoft Access MVP


Keith said:
When I go to design, I added it to the modules (the last one in the list ie.
Tables, Forms, Reports, Pages, Macros, Modules). When I do a search it's in
the class module. Yes, I compiled it but after that I can't compile it again,
it won't give me the option.

Any other suggestions? Thanks again for your help.


Duane Hookom said:
A user-defined function is nearly the same as any other Access/VBA function.
Assuming a function like Mid(), the developer provides 2 or 3 arguments like
Mid([MyTextValue], 4,6)
You have done the same with the WorkTime() function. I would expect the
appropriate syntax would be:
worktime([assigned to ss],[ss rejected to sales],#8:00:00#,#18:00:00#,60)
AS Workedtime

Did you add the function to a form or report module? Have you attempted to
compile your application? If you open any module and search for "Function
Worktime", where do you find it?
--
Duane Hookom
Microsoft Access MVP


Keith said:
I don't know if I do. I don't understand how the function knows to replace my
fields [assigned to ss] and [ss rejected to sales] in place of the [dtmstart]
and [dtmend].


Yes, the query is running in Access and I do have a bracket missing but it's
still getting the same error.

Maybe I need to scrap this and think of another way to get my results. I'm
just trying to figure out how much time was spent on a task in working
minutes. It seems like it should be so much easier than this...



:

Apparently you understand how to pass values (field or other) to the function.

Is your query running in Access?
Your SQL syntax has issues. I can't see how it would return anything. There
is at least one bracket missing and I don't think the dtmTime... values are
correct.
--
Duane Hookom
Microsoft Access MVP


:

Thanks Ken for spending so much time with this...

Maybe I'm calling it wrong.
In my query, it's:
SELECT Sales Support Specialist], [Assigned to SS], [SS Rejected to Sales],
worktime([assigned to ss],[ss rejected to sales],#2/01/2008 8:0:0#,#2/29/2008
18:0:0#,60) AS Workedtime
FROM order_log
WHERE (((order_log.[SS Rejected to Sales]) Is Not Null));


Error message "undefined expression 'worktime' in expression.


Public Function WorkTime(dtmStart As Date, _
dtmEnd As Date, _
dtmTimeIn As Date, _
dtmTimeOut As Date, _
intLunchMinutes As Integer) As String

Const HOURSINDAY = 24
Const MINUTESINDAY = 1440

Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
Dim dtmDay As Date

For dtmDay = DateValue(dtmStart) To DateValue(dtmEnd)
' if weekend do nothing
If Weekday(dtmDay, vbMonday) < 6 Then
Select Case dtmDay
Case Is = DateValue(dtmStart) ' first day
' get time from start until end of work day
dblDuration = dblDuration + (dtmTimeOut -
TimeValue(dtmStart))
' subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
' if first and last day are same day then subtract
' time from time out to end of time worked
If dtmDay = DateValue(dtmEnd) Then
dblDuration = dblDuration - (dtmTimeOut -
TimeValue(dtmEnd))
End If
Case Is = DateValue(dtmEnd) ' last day
' get time from start of work day until end time
dblDuration = dblDuration + (TimeValue(dtmEnd) -
dtmTimeIn)
' if start time after lunch subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
Case Else
' if last day is day after first day do nothing more
' otherwise compute time worked for a full day
If DateValue(dtmEnd) - DateValue(dtmStart) > 1 Then
dblDuration = dblDuration + (dtmTimeOut - dtmTimeIn
- (intLunchMinutes / MINUTESINDAY))
End If
End Select
End If
Next dtmDay

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

WorkTime = lngHours & strMinutesSeconds

End Function




:

I'm not sure what all this means :-(
--
Duane Hookom
Microsoft Access MVP


:

Library states "order"
Class "moddatetimecalcs"
member "worktime"

is this correct?
___________________________

:

My guess was that you had named the function and the module the same. This
would be one possible cause of your symptom. I recommend using a naming
convention so that module names begin with either "bas" or "mod". My form
names generally begin with "frm" and reports "rpt". The use of this
"standard" causes much debate but it provides some much needed consistency in
my programming life.

If changing the name of the module didn't fix the problem then you might
search all of your VBA to see if you have two or more copies of the same
function name.
--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane,
I changed the module name to modDateTimeCalcs but I don't understand the
second part of your statement "Then find and use a naming convention that
doesn't allow duplication like this." I'm still getting the same error so I
think I just need this second part...sorry I'm not getting it.

Now this is a Class module correct?


:

Did you name the module the same as the function? If so, change it to
"modDateTimeCalcs". Then find and use a naming convention that doesn't allow
duplication like this.

--
Duane Hookom
Microsoft Access MVP


:

Can someone please help with the following function that was provided by Ken
Sheridan. I pasted this in the VBA but I guess I need to replace some of the
field names with the names of my fields correct? I keep getting an undefined
function 'worktime' so maybe I pasted it incorrectly in Visual Basic?
Thanks for any help,
Keith


To get the actual time worked you could call a VBA function. The following
is adapted from one of mine for computing elapsed times and should give you
the correct duration in the format hh:nn:ss subject to a few assumptions:

1. That the start and end date/time values can only be on a weekday.
2. That a lunch break of fixed length is taken each day.
3. That lunch breaks are taken on every day, including the first and last.

Public Function WorkTime(dtmStart As Date, _
dtmEnd As Date, _
dtmTimeIn As Date, _
dtmTimeOut As Date, _
intLunchMinutes As Integer) As String

Const HOURSINDAY = 24
Const MINUTESINDAY = 1440

Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
Dim dtmDay As Date

For dtmDay = DateValue(dtmStart) To DateValue(dtmEnd)
' if weekend do nothing
If Weekday(dtmDay, vbMonday) < 6 Then
Select Case dtmDay
Case Is = DateValue(dtmStart) ' first day
' get time from start until end of work day
dblDuration = dblDuration + (dtmTimeOut -
TimeValue(dtmStart))
' subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
' if first and last day are same day then subtract
' time from time out to end of time worked
If dtmDay = DateValue(dtmEnd) Then
dblDuration = dblDuration - (dtmTimeOut -
TimeValue(dtmEnd))
End If
Case Is = DateValue(dtmEnd) ' last day
' get time from start of work day until end time
dblDuration = dblDuration + (TimeValue(dtmEnd) -
dtmTimeIn)
' if start time after lunch subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
Case Else
' if last day is day after first day do nothing more
' otherwise compute time worked for a full day
If DateValue(dtmEnd) - DateValue(dtmStart) > 1 Then
dblDuration = dblDuration + (dtmTimeOut - dtmTimeIn
- (intLunchMinutes / MINUTESINDAY))
End If
End Select
End If
Next dtmDay

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

WorkTime = lngHours & strMinutesSeconds

End Function

You'll probably find that some longer lines in the code have been split over
two lines in your newsreader, so you might need to correct this after pasting
the function into a standard module in your database.

Assuming a day from 8:00 AM to 6:00PM with a 60 minute lunch break you'd
call the function in a query like so:

SELECT event, user, startdate, enddate,
WorkTime(startdate, enddate, #08:00#, #18:00#, 60)
AS workedtime
FROM YourTable;

or you could call it in the same way in the ControlSource of a computed
control in a form or report.

Ken Sheridan
Stafford, England

:

Given the following table tblEvents,

event user startdate enddate
elapsed work
0156 Pam 10/02/200710:15 20/02/2007 15:44 10d 5h 29m ???????

I can´t figure out a query to update multiple records with real work times
between these two points, in minutes/hours, excluding nonworking times (sat,
sun, 18:00/8:00, lunch...)
Click to expand...
Click to expand...
Click to expand...
 
K

Keith

How do I make it into a standard module? Everytime, it puts it into a class
module.

Duane Hookom said:
Is there some reason you created a "class" module rather than a standard
module? Try delete the function from the class module and paste it into a
standard module.
--
Duane Hookom
Microsoft Access MVP


Keith said:
When I go to design, I added it to the modules (the last one in the list ie.
Tables, Forms, Reports, Pages, Macros, Modules). When I do a search it's in
the class module. Yes, I compiled it but after that I can't compile it again,
it won't give me the option.

Any other suggestions? Thanks again for your help.


Duane Hookom said:
A user-defined function is nearly the same as any other Access/VBA function.
Assuming a function like Mid(), the developer provides 2 or 3 arguments like
Mid([MyTextValue], 4,6)
You have done the same with the WorkTime() function. I would expect the
appropriate syntax would be:
worktime([assigned to ss],[ss rejected to sales],#8:00:00#,#18:00:00#,60)
AS Workedtime

Did you add the function to a form or report module? Have you attempted to
compile your application? If you open any module and search for "Function
Worktime", where do you find it?
--
Duane Hookom
Microsoft Access MVP


:

I don't know if I do. I don't understand how the function knows to replace my
fields [assigned to ss] and [ss rejected to sales] in place of the [dtmstart]
and [dtmend].


Yes, the query is running in Access and I do have a bracket missing but it's
still getting the same error.

Maybe I need to scrap this and think of another way to get my results. I'm
just trying to figure out how much time was spent on a task in working
minutes. It seems like it should be so much easier than this...



:

Apparently you understand how to pass values (field or other) to the function.

Is your query running in Access?
Your SQL syntax has issues. I can't see how it would return anything. There
is at least one bracket missing and I don't think the dtmTime... values are
correct.
--
Duane Hookom
Microsoft Access MVP


:

Thanks Ken for spending so much time with this...

Maybe I'm calling it wrong.
In my query, it's:
SELECT Sales Support Specialist], [Assigned to SS], [SS Rejected to Sales],
worktime([assigned to ss],[ss rejected to sales],#2/01/2008 8:0:0#,#2/29/2008
18:0:0#,60) AS Workedtime
FROM order_log
WHERE (((order_log.[SS Rejected to Sales]) Is Not Null));


Error message "undefined expression 'worktime' in expression.


Public Function WorkTime(dtmStart As Date, _
dtmEnd As Date, _
dtmTimeIn As Date, _
dtmTimeOut As Date, _
intLunchMinutes As Integer) As String

Const HOURSINDAY = 24
Const MINUTESINDAY = 1440

Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
Dim dtmDay As Date

For dtmDay = DateValue(dtmStart) To DateValue(dtmEnd)
' if weekend do nothing
If Weekday(dtmDay, vbMonday) < 6 Then
Select Case dtmDay
Case Is = DateValue(dtmStart) ' first day
' get time from start until end of work day
dblDuration = dblDuration + (dtmTimeOut -
TimeValue(dtmStart))
' subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
' if first and last day are same day then subtract
' time from time out to end of time worked
If dtmDay = DateValue(dtmEnd) Then
dblDuration = dblDuration - (dtmTimeOut -
TimeValue(dtmEnd))
End If
Case Is = DateValue(dtmEnd) ' last day
' get time from start of work day until end time
dblDuration = dblDuration + (TimeValue(dtmEnd) -
dtmTimeIn)
' if start time after lunch subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
Case Else
' if last day is day after first day do nothing more
' otherwise compute time worked for a full day
If DateValue(dtmEnd) - DateValue(dtmStart) > 1 Then
dblDuration = dblDuration + (dtmTimeOut - dtmTimeIn
- (intLunchMinutes / MINUTESINDAY))
End If
End Select
End If
Next dtmDay

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

WorkTime = lngHours & strMinutesSeconds

End Function




:

I'm not sure what all this means :-(
--
Duane Hookom
Microsoft Access MVP


:

Library states "order"
Class "moddatetimecalcs"
member "worktime"

is this correct?
___________________________

:

My guess was that you had named the function and the module the same. This
would be one possible cause of your symptom. I recommend using a naming
convention so that module names begin with either "bas" or "mod". My form
names generally begin with "frm" and reports "rpt". The use of this
"standard" causes much debate but it provides some much needed consistency in
my programming life.

If changing the name of the module didn't fix the problem then you might
search all of your VBA to see if you have two or more copies of the same
function name.
--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane,
I changed the module name to modDateTimeCalcs but I don't understand the
second part of your statement "Then find and use a naming convention that
doesn't allow duplication like this." I'm still getting the same error so I
think I just need this second part...sorry I'm not getting it.

Now this is a Class module correct?


:

Did you name the module the same as the function? If so, change it to
"modDateTimeCalcs". Then find and use a naming convention that doesn't allow
duplication like this.

--
Duane Hookom
Microsoft Access MVP


:

Can someone please help with the following function that was provided by Ken
Sheridan. I pasted this in the VBA but I guess I need to replace some of the
field names with the names of my fields correct? I keep getting an undefined
function 'worktime' so maybe I pasted it incorrectly in Visual Basic?
Thanks for any help,
Keith


To get the actual time worked you could call a VBA function. The following
is adapted from one of mine for computing elapsed times and should give you
the correct duration in the format hh:nn:ss subject to a few assumptions:

1. That the start and end date/time values can only be on a weekday.
2. That a lunch break of fixed length is taken each day.
3. That lunch breaks are taken on every day, including the first and last.

Public Function WorkTime(dtmStart As Date, _
dtmEnd As Date, _
dtmTimeIn As Date, _
dtmTimeOut As Date, _
intLunchMinutes As Integer) As String

Const HOURSINDAY = 24
Const MINUTESINDAY = 1440

Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
Dim dtmDay As Date

For dtmDay = DateValue(dtmStart) To DateValue(dtmEnd)
' if weekend do nothing
If Weekday(dtmDay, vbMonday) < 6 Then
Select Case dtmDay
Case Is = DateValue(dtmStart) ' first day
' get time from start until end of work day
dblDuration = dblDuration + (dtmTimeOut -
TimeValue(dtmStart))
' subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
' if first and last day are same day then subtract
' time from time out to end of time worked
If dtmDay = DateValue(dtmEnd) Then
dblDuration = dblDuration - (dtmTimeOut -
TimeValue(dtmEnd))
End If
Case Is = DateValue(dtmEnd) ' last day
' get time from start of work day until end time
dblDuration = dblDuration + (TimeValue(dtmEnd) -
dtmTimeIn)
' if start time after lunch subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
Case Else
' if last day is day after first day do nothing more
' otherwise compute time worked for a full day
If DateValue(dtmEnd) - DateValue(dtmStart) > 1 Then
dblDuration = dblDuration + (dtmTimeOut - dtmTimeIn
- (intLunchMinutes / MINUTESINDAY))
End If
End Select
End If
Next dtmDay

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

WorkTime = lngHours & strMinutesSeconds

End Function

You'll probably find that some longer lines in the code have been split over
two lines in your newsreader, so you might need to correct this after pasting
the function into a standard module in your database.

Assuming a day from 8:00 AM to 6:00PM with a 60 minute lunch break you'd
call the function in a query like so:

SELECT event, user, startdate, enddate,
WorkTime(startdate, enddate, #08:00#, #18:00#, 60)
AS workedtime
FROM YourTable;

or you could call it in the same way in the ControlSource of a computed
control in a form or report.

Ken Sheridan
Stafford, England

:
Click to expand...
Click to expand...
Click to expand...
 
K

Keith

YOU ARE A GENIUS!!! THANK YOU SO MUCH! Wish I could return the favor to
you...Especially for not giving up on me and seeing this through.
THANK YOU THANK YOU!!!

Duane Hookom said:
Is there some reason you created a "class" module rather than a standard
module? Try delete the function from the class module and paste it into a
standard module.
--
Duane Hookom
Microsoft Access MVP


Keith said:
When I go to design, I added it to the modules (the last one in the list ie.
Tables, Forms, Reports, Pages, Macros, Modules). When I do a search it's in
the class module. Yes, I compiled it but after that I can't compile it again,
it won't give me the option.

Any other suggestions? Thanks again for your help.


Duane Hookom said:
A user-defined function is nearly the same as any other Access/VBA function.
Assuming a function like Mid(), the developer provides 2 or 3 arguments like
Mid([MyTextValue], 4,6)
You have done the same with the WorkTime() function. I would expect the
appropriate syntax would be:
worktime([assigned to ss],[ss rejected to sales],#8:00:00#,#18:00:00#,60)
AS Workedtime

Did you add the function to a form or report module? Have you attempted to
compile your application? If you open any module and search for "Function
Worktime", where do you find it?
--
Duane Hookom
Microsoft Access MVP


:

I don't know if I do. I don't understand how the function knows to replace my
fields [assigned to ss] and [ss rejected to sales] in place of the [dtmstart]
and [dtmend].


Yes, the query is running in Access and I do have a bracket missing but it's
still getting the same error.

Maybe I need to scrap this and think of another way to get my results. I'm
just trying to figure out how much time was spent on a task in working
minutes. It seems like it should be so much easier than this...



:

Apparently you understand how to pass values (field or other) to the function.

Is your query running in Access?
Your SQL syntax has issues. I can't see how it would return anything. There
is at least one bracket missing and I don't think the dtmTime... values are
correct.
--
Duane Hookom
Microsoft Access MVP


:

Thanks Ken for spending so much time with this...

Maybe I'm calling it wrong.
In my query, it's:
SELECT Sales Support Specialist], [Assigned to SS], [SS Rejected to Sales],
worktime([assigned to ss],[ss rejected to sales],#2/01/2008 8:0:0#,#2/29/2008
18:0:0#,60) AS Workedtime
FROM order_log
WHERE (((order_log.[SS Rejected to Sales]) Is Not Null));


Error message "undefined expression 'worktime' in expression.


Public Function WorkTime(dtmStart As Date, _
dtmEnd As Date, _
dtmTimeIn As Date, _
dtmTimeOut As Date, _
intLunchMinutes As Integer) As String

Const HOURSINDAY = 24
Const MINUTESINDAY = 1440

Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
Dim dtmDay As Date

For dtmDay = DateValue(dtmStart) To DateValue(dtmEnd)
' if weekend do nothing
If Weekday(dtmDay, vbMonday) < 6 Then
Select Case dtmDay
Case Is = DateValue(dtmStart) ' first day
' get time from start until end of work day
dblDuration = dblDuration + (dtmTimeOut -
TimeValue(dtmStart))
' subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
' if first and last day are same day then subtract
' time from time out to end of time worked
If dtmDay = DateValue(dtmEnd) Then
dblDuration = dblDuration - (dtmTimeOut -
TimeValue(dtmEnd))
End If
Case Is = DateValue(dtmEnd) ' last day
' get time from start of work day until end time
dblDuration = dblDuration + (TimeValue(dtmEnd) -
dtmTimeIn)
' if start time after lunch subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
Case Else
' if last day is day after first day do nothing more
' otherwise compute time worked for a full day
If DateValue(dtmEnd) - DateValue(dtmStart) > 1 Then
dblDuration = dblDuration + (dtmTimeOut - dtmTimeIn
- (intLunchMinutes / MINUTESINDAY))
End If
End Select
End If
Next dtmDay

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

WorkTime = lngHours & strMinutesSeconds

End Function




:

I'm not sure what all this means :-(
--
Duane Hookom
Microsoft Access MVP


:

Library states "order"
Class "moddatetimecalcs"
member "worktime"

is this correct?
___________________________

:

My guess was that you had named the function and the module the same. This
would be one possible cause of your symptom. I recommend using a naming
convention so that module names begin with either "bas" or "mod". My form
names generally begin with "frm" and reports "rpt". The use of this
"standard" causes much debate but it provides some much needed consistency in
my programming life.

If changing the name of the module didn't fix the problem then you might
search all of your VBA to see if you have two or more copies of the same
function name.
--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane,
I changed the module name to modDateTimeCalcs but I don't understand the
second part of your statement "Then find and use a naming convention that
doesn't allow duplication like this." I'm still getting the same error so I
think I just need this second part...sorry I'm not getting it.

Now this is a Class module correct?


:

Did you name the module the same as the function? If so, change it to
"modDateTimeCalcs". Then find and use a naming convention that doesn't allow
duplication like this.

--
Duane Hookom
Microsoft Access MVP


:

Can someone please help with the following function that was provided by Ken
Sheridan. I pasted this in the VBA but I guess I need to replace some of the
field names with the names of my fields correct? I keep getting an undefined
function 'worktime' so maybe I pasted it incorrectly in Visual Basic?
Thanks for any help,
Keith


To get the actual time worked you could call a VBA function. The following
is adapted from one of mine for computing elapsed times and should give you
the correct duration in the format hh:nn:ss subject to a few assumptions:

1. That the start and end date/time values can only be on a weekday.
2. That a lunch break of fixed length is taken each day.
3. That lunch breaks are taken on every day, including the first and last.

Public Function WorkTime(dtmStart As Date, _
dtmEnd As Date, _
dtmTimeIn As Date, _
dtmTimeOut As Date, _
intLunchMinutes As Integer) As String

Const HOURSINDAY = 24
Const MINUTESINDAY = 1440

Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
Dim dtmDay As Date

For dtmDay = DateValue(dtmStart) To DateValue(dtmEnd)
' if weekend do nothing
If Weekday(dtmDay, vbMonday) < 6 Then
Select Case dtmDay
Case Is = DateValue(dtmStart) ' first day
' get time from start until end of work day
dblDuration = dblDuration + (dtmTimeOut -
TimeValue(dtmStart))
' subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
' if first and last day are same day then subtract
' time from time out to end of time worked
If dtmDay = DateValue(dtmEnd) Then
dblDuration = dblDuration - (dtmTimeOut -
TimeValue(dtmEnd))
End If
Case Is = DateValue(dtmEnd) ' last day
' get time from start of work day until end time
dblDuration = dblDuration + (TimeValue(dtmEnd) -
dtmTimeIn)
' if start time after lunch subtract lunch time
dblDuration = dblDuration - (intLunchMinutes /
MINUTESINDAY)
Case Else
' if last day is day after first day do nothing more
' otherwise compute time worked for a full day
If DateValue(dtmEnd) - DateValue(dtmStart) > 1 Then
dblDuration = dblDuration + (dtmTimeOut - dtmTimeIn
- (intLunchMinutes / MINUTESINDAY))
End If
End Select
End If
Next dtmDay

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

WorkTime = lngHours & strMinutesSeconds

End Function

You'll probably find that some longer lines in the code have been split over
two lines in your newsreader, so you might need to correct this after pasting
the function into a standard module in your database.

Assuming a day from 8:00 AM to 6:00PM with a 60 minute lunch break you'd
call the function in a query like so:

SELECT event, user, startdate, enddate,
WorkTime(startdate, enddate, #08:00#, #18:00#, 60)
AS workedtime
FROM YourTable;

or you could call it in the same way in the ControlSource of a computed
control in a form or report.

Ken Sheridan
Stafford, England

:
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calcworkdays Revision? 2
Help with my function vba code 1
Workday calculations for weekends 9
Function Strangeness 8
Custom Function 6
Date & Time Format in Ms Access Queries 1
Data type mismatch in criteria expression 4
adding work days to a begin date to derive the end date 1

Top