Totaling time

  • Thread starter Thread starter AnnP
  • Start date Start date
A

AnnP

I have a report in which I need to show a total number of
hours, and minutes a staff member was absent from work.
I have a field that shows the date and time absence began
and another to show when the absence ended. Using the
expression below, I am able to get the amount of time for
each occurrence. However, in the report, I need to group
the employees showing a total of time they were absent
during a month. I have not been able to total the results
of this expression. Please help.

=IIf(([AbsenceBegan] or [AbsenceEnded] Is Not
Null),GetElapsedTime([AbsenceEnded]-[ AbsenceBegan])," ")
 
1) it would help to know what the function GetElapsedTime() does
2) I would think the function returns a numeric value so the , " ") should
probably be , 0)
3) Are you expected the "[AbsenceBegan] or [AbsenceEnded] Is Not Null" to
check both fields for null? It only checks AbsenceEnded.
 
Sorry, I wasn't providing enough info and not being
clear. Hope this helps.

1) GetElapsedTime is a module that produces the number of
hours and minutes that have elapsed from [AbsenceBegan] to
[AbsenceEnded] and is enter in a field named [TimeAbsent]
The module is written as follows:

Function GetElapsedTime(interval)

Dim totalhours As Long, totalminutes As Long, totalseconds
As Long
Dim days As Long, hours As Long, Minutes As Long, seconds
As Long


totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
totalseconds = Int(CSng(interval * 86400))
hours = totalhours Mod 24
Minutes = totalminutes Mod 60
seconds = totalseconds Mod 60

GetElapsedTime = days & " Days " & hours & " Hrs. " &
Minutes & " Mins."

2)I changed " " to "0"
3)AbsenceEnded is the only field that needs to be checked,
for null, so I deleted the [AbsenceBegan] from the
expression.

Can you help me write an expression for a field titled
[TotalTimeAbsent] that will be placed in a Group Footer,
giving the total number of hours and minutes in the
[TimeAbsent] fields for that group.

Thanks you any help.


-----Original Message-----
1) it would help to know what the function GetElapsedTime () does
2) I would think the function returns a numeric value so the , " ") should
probably be , 0)
3) Are you expected the "[AbsenceBegan] or [AbsenceEnded] Is Not Null" to
check both fields for null? It only checks AbsenceEnded.

--
Duane Hookom
MS Access MVP
--

I have a report in which I need to show a total number of
hours, and minutes a staff member was absent from work.
I have a field that shows the date and time absence began
and another to show when the absence ended. Using the
expression below, I am able to get the amount of time for
each occurrence. However, in the report, I need to group
the employees showing a total of time they were absent
during a month. I have not been able to total the results
of this expression. Please help.

=IIf(([AbsenceBegan] or [AbsenceEnded] Is Not
Null),GetElapsedTime([AbsenceEnded]-[ AbsenceBegan])," ")
Click to expand...


.
Click to expand...
 
You could try:
=GetElapsedTime(Sum( Nz([AbsenceEnded],[AbsenseBegan]) - [AbsenseBegan]) )

--
Duane Hookom
MS Access MVP
--

AnnP said:
Sorry, I wasn't providing enough info and not being
clear. Hope this helps.

1) GetElapsedTime is a module that produces the number of
hours and minutes that have elapsed from [AbsenceBegan] to
[AbsenceEnded] and is enter in a field named [TimeAbsent]
The module is written as follows:

Function GetElapsedTime(interval)

Dim totalhours As Long, totalminutes As Long, totalseconds
As Long
Dim days As Long, hours As Long, Minutes As Long, seconds
As Long


totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
totalseconds = Int(CSng(interval * 86400))
hours = totalhours Mod 24
Minutes = totalminutes Mod 60
seconds = totalseconds Mod 60

GetElapsedTime = days & " Days " & hours & " Hrs. " &
Minutes & " Mins."

2)I changed " " to "0"
3)AbsenceEnded is the only field that needs to be checked,
for null, so I deleted the [AbsenceBegan] from the
expression.

Can you help me write an expression for a field titled
[TotalTimeAbsent] that will be placed in a Group Footer,
giving the total number of hours and minutes in the
[TimeAbsent] fields for that group.

Thanks you any help.


-----Original Message-----
1) it would help to know what the function GetElapsedTime () does
2) I would think the function returns a numeric value so the , " ") should
probably be , 0)
3) Are you expected the "[AbsenceBegan] or [AbsenceEnded] Is Not Null" to
check both fields for null? It only checks AbsenceEnded.

--
Duane Hookom
MS Access MVP
--

I have a report in which I need to show a total number of
hours, and minutes a staff member was absent from work.
I have a field that shows the date and time absence began
and another to show when the absence ended. Using the
expression below, I am able to get the amount of time for
each occurrence. However, in the report, I need to group
the employees showing a total of time they were absent
during a month. I have not been able to total the results
of this expression. Please help.

=IIf(([AbsenceBegan] or [AbsenceEnded] Is Not
Null),GetElapsedTime([AbsenceEnded]-[ AbsenceBegan])," ")
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top