How do I sum this calculated control?

G

Guest

In a bound report, I have two bound controls (names: txtEndTime &
txtStartTime) and an unbound calculated control (txtElapsedTime). The two
bound controls have General Date as their format property. The unbound
calculated control's Control Source contains:
"GetElapsedTime([txtEndTime]-[txtStartTime])" (W/O quotes) from the
GetElapsedTime() function attached to the report, as follows:

Option Explicit

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

days = Int(CSng(interval))
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 & " Hours " & Minutes & " Minutes "
& Seconds & " Seconds "

End Function

Thanks to this code, etc., when the report is opened and previewed, the
unbound calculated control (txtElapsedTime) displays a calculation for each
Start Time and End Time, for one example:

"4 Days 9 Hours 41 Minutes 0 Seconds" (W/O quotes!) by subtracting
05/10/1995 4:57:00 PM from 05/15/1995 2:38:00 AM.

This report may contain multiple Start Times and End Times. My question:
How can I sum up all the calculations in the txtElapsedTime control to get a
grand total? Any help will be very much appreciated.
 
G

Guest

Duane, You got it. Worked perfectly! Thank-you! Denny G.

Duane Hookom said:
Try:
=GetElapsedTime(Sum([EndTimeField]-[StartTimeField]))

--
Duane Hookom
MS Access MVP


Denny G. said:
In a bound report, I have two bound controls (names: txtEndTime &
txtStartTime) and an unbound calculated control (txtElapsedTime). The two
bound controls have General Date as their format property. The unbound
calculated control's Control Source contains:
"GetElapsedTime([txtEndTime]-[txtStartTime])" (W/O quotes) from the
GetElapsedTime() function attached to the report, as follows:

Option Explicit

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

days = Int(CSng(interval))
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 & " Hours " & Minutes & " Minutes
"
& Seconds & " Seconds "

End Function

Thanks to this code, etc., when the report is opened and previewed, the
unbound calculated control (txtElapsedTime) displays a calculation for
each
Start Time and End Time, for one example:

"4 Days 9 Hours 41 Minutes 0 Seconds" (W/O quotes!) by subtracting
05/10/1995 4:57:00 PM from 05/15/1995 2:38:00 AM.

This report may contain multiple Start Times and End Times. My question:
How can I sum up all the calculations in the txtElapsedTime control to get
a
grand total? Any help will be very much appreciated.
Click to expand...
Click to expand...
 
G

Guest

Duane -

I'm trying something similar with no luck:

[TravelDays] =DateDiff("d",[StartDate],[EndDate])
[TravelDaysSum] =SUM([TravelDays])

but when i run the report, i'm asked for the value of the TravelDays param.

any ideas?

/amelia

Duane Hookom said:
Try:
=GetElapsedTime(Sum([EndTimeField]-[StartTimeField]))

--
Duane Hookom
MS Access MVP


Denny G. said:
In a bound report, I have two bound controls (names: txtEndTime &
txtStartTime) and an unbound calculated control (txtElapsedTime). The two
bound controls have General Date as their format property. The unbound
calculated control's Control Source contains:
"GetElapsedTime([txtEndTime]-[txtStartTime])" (W/O quotes) from the
GetElapsedTime() function attached to the report, as follows:

Option Explicit

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

days = Int(CSng(interval))
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 & " Hours " & Minutes & " Minutes
"
& Seconds & " Seconds "

End Function

Thanks to this code, etc., when the report is opened and previewed, the
unbound calculated control (txtElapsedTime) displays a calculation for
each
Start Time and End Time, for one example:

"4 Days 9 Hours 41 Minutes 0 Seconds" (W/O quotes!) by subtracting
05/10/1995 4:57:00 PM from 05/15/1995 2:38:00 AM.

This report may contain multiple Start Times and End Times. My question:
How can I sum up all the calculations in the txtElapsedTime control to get
a
grand total? Any help will be very much appreciated.
Click to expand...
Click to expand...
 
D

Duane Hookom

You can't sum a control. TravelDays is a control. You can sum the expression
from a control:
=Sum(DateDiff("d",[StartDate],[EndDate]))
Or
=Sum([EndDate]-[StartDate])



--
Duane Hookom
MS Access MVP


aaearhart said:
Duane -

I'm trying something similar with no luck:

[TravelDays] =DateDiff("d",[StartDate],[EndDate])
[TravelDaysSum] =SUM([TravelDays])

but when i run the report, i'm asked for the value of the TravelDays
param.

any ideas?

/amelia

Duane Hookom said:
Try:
=GetElapsedTime(Sum([EndTimeField]-[StartTimeField]))

--
Duane Hookom
MS Access MVP


Denny G. said:
In a bound report, I have two bound controls (names: txtEndTime &
txtStartTime) and an unbound calculated control (txtElapsedTime). The
two
bound controls have General Date as their format property. The unbound
calculated control's Control Source contains:
"GetElapsedTime([txtEndTime]-[txtStartTime])" (W/O quotes) from the
GetElapsedTime() function attached to the report, as follows:

Option Explicit

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

days = Int(CSng(interval))
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 & " Hours " & Minutes & "
Minutes
"
& Seconds & " Seconds "

End Function

Thanks to this code, etc., when the report is opened and previewed, the
unbound calculated control (txtElapsedTime) displays a calculation for
each
Start Time and End Time, for one example:

"4 Days 9 Hours 41 Minutes 0 Seconds" (W/O quotes!) by subtracting
05/10/1995 4:57:00 PM from 05/15/1995 2:38:00 AM.

This report may contain multiple Start Times and End Times. My
question:
How can I sum up all the calculations in the txtElapsedTime control to
get
a
grand total? Any help will be very much appreciated.
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

Text box Formula Error Message 2
Calculating hours on a timeclock form 7
Calculating Time 5
Time Function 3
Elapsed Time Assistance 3
Unresolved Issue about totaling Downtime Hours 1
Problem w/Date time function 2
Function and Query 4

Top