Sum of short time values in report footer

[jackjee]

Hi
I am trying to sum the field values in an access report footer. The source is from a cross tab query and the values are in the format of hh:nn, but some of the values are greater than 24 hour such as 77:50, 24:05 etc.
I have unbound textboxes placed in the access report footer and expected this text boxes need to give me the sum of each staff. I tried to set the control source as =sum([staff1]) etc. but no luck
Hope someone can guide me in the right direction to write criteria for getting the the sum values when the report detail section (source fields) has time values. My master data doesn't have any dates, only duration is available in the table as I explained

[NeoPa]

JackJee:
I have unbound textboxes placed in the access report footer and expected this text boxes need to give me the sum of each staff.

How do you expect meaningful replies when you clearly haven't bothered to read your question through before posting.

JackJee:
I tried to set the control source as =sum([staff1]) etc. but no luck

You tell us very little here except that it doesn't do what you expect it to. Inferring from stuff unexplained it seems clear you've not Copy/Pasted the formula otherwiuse it would read =Sum([staff1]) so there's not much here we can rely on at all.

I assume that [staff1] (or maybe [Staff1] even) is the name of a Control in the Detail section of the report. It may be a Field name of course, which would explain it failing. If the name of the Control & that of the Field are identical I expect you'll also have problems.

What you need, and what should work quite reliably, is a Control in the Footer section of the Report that has a ControlSource of :
=Sum([X]) where X is the unique name of a Control in the Detail section.

I read your question a number of times & still I don't know whether you want to lose any day parts of the duration values so you end up with just a time value or you want to include all values in full. I'll just say that formatting doesn't change the value. So, 2.25 formatted as a Date/Time value comes out as :
1/1/1900 06:00:00
If you display this in a Control showing just the time part (06:00:00) then that has no effect on the value as used in a calculation. If you do need to tally the sum of values as seen then you would need another Control in the Detail section set to :
=TimeValue([X]) and then use this Control in your Sum([Y]) calculation instead.

Hopefully I've given answers to all you need, though obviously I had to guess more than I like to at what you were actually asking.

[jackjee]

Hi NeoPa
Thank you for the quick reply and apologies for creating a confusion and unclarity with my request.
The main table has fields such as Staff, Taskdate (as "dd-mmm-yy"'), Duration ("hh:nn"), Tasks etc.
A select query is used to select all the fields and an additional custom field for converting the duration to seconds with the use of a function I got from google search.
Based on this select query, I create a crosstab query to summarize the data and create reports. The crosstab 'value' field is populated with an expression as " IIf(IsNull(Sum([seconds])),"00:00",totime(Sum([seconds])))" where 'seconds' is the custom field from select query. The column heading of the crosstab is based on the 'Staff' field of the select query and I have set it with fixed staff names as column heading. The report is based on this crosstab query. The report details section has controls named as staff1,staff2, staff3,staff4 etc. which are the staff names available in my master data.
The values in these fields are the sum of the duration (hh:nn format) each staff have.
As you can see from the expression, the sum of seconds are converted back to time format ("hh:nn") with the help of another function I got from google search.
Converting 'Duration' hh:nn to seconds, I use this function

Public Function toseconds(sDate As String) As Long
 
    Dim hours           As String
    Dim minutes         As String
 
    hours = Trim(Split(sDate, ":")(0))
    minutes = Trim(Split(sDate, ":")(1))
 
    toseconds = CLng(hours) * 3600 + CLng(minutes) * 60 
 
End Function

And the function used to convert seconds back to hh:nn is below

Public Function totime(dblSeconds As Double) As String
 
    Dim hours           As Long
    Dim minutes         As Long
    Dim dUsedSeconds    As Long
 
    Dim sHours          As String
    Dim sMinutes        As String
 
    dUsedSeconds = CLng(dblSeconds)
 
    hours = Int(dUsedSeconds / 3600)
    minutes = Int((dUsedSeconds Mod 3600) / 60)
 
 
    sHours = Format(hours, "00")
    sMinutes = Format(minutes, "00")
 
 
    totime = sHours & ":" & sMinutes 
 
End Function
 

Now the report record source is this crosstab query and the detail section of the report populates as expected. But my challenge is to get the "TOTAL Duration" of each staff in the footer of the report. Hope I explained what I have already done and stuck with the summing of the staff duration. Hope I have explained everything required for a support from you or please let me know if more clarity required on my question. Thank you for the time and patience from the experts remain same as in the past

[NeoPa]

Hi again Jack.

This seems a lot better so thank you for your renewed efforts. It does indicate a certain lack of understanding in some places but then hopefully we can help with that. After all you wouldn't come for help if you needed none ;-)

Let me first refer back to one of the comments from my earlier post where I explained that a value, and the formatted string of that value, are entirely separate entities and should never be confused. As a general rule calculations should be done using the value itself and only ever format results for viewing at the point where they need to be viewed. This will truly simplify your life.

Another very quick tip I'll share before proceeding as it can also help with all your work :
Avoid giving names to things (Anything - anywhere.) that are all in lower case. Intellisense will reflect the case you use so normally, if you see anything in all lower case, it means it's wrong. Intellisense hasn't recognised it or it wasn't Copy/Pasted from an actual project but someone's typed it out either from scratch or from a Word Processor. If you ever use names with all lower case though, you miss out on this extremely helpful benefit - thus giving yourself ongoing problems.

I see that the Function you posted is called toseconds() and it seems clear that you're taking an input of a formatted string instead of the actual value that should be available. This can work but you're making extra trouble for yourself. Seconds from a time value is much easier (& more reliable) as :

Public Function ToSeconds(sDate As Date) As Long
    ToSeconds = CLng(sDate * 86400&)
End Function

To convert back is equally simple :

Public Function ToTime(lngSeconds As Long) As Date
    ToTime = CDate(lngSeconds / 86400#)
End Function

Formatting such a returned value as HH:mm (Hours:Minutes) is as simple as :

Format(ToTime(lngValue),"HH:nn")

Now, it seems I'm still a little in the dark as to what your problem is but I have a suspicion that your troubles are related to trying to sum string values. That simply isn't going to work. If you take away the understanding that you need to work with the values as they come to you, and only ever format them when it comes to the point of showing the results, then I suspect all problems will melt away as mist.

If not, share again the point to which you have reached, including a clear description of what fails (including any error messages if there are any of course) and we will try to help from that point.

PS. Though I always try to format my code suggestions as you'd expect if Copy/Pasted I have to admit to typing it out from scratch in most cases. I do focus on ensuring I don't introduce errors that way but honesty impels me to admit I break my own rules. In my defence I'm quite experienced and quite successful at avoiding errors.

[yuvsaha123]

thanks for the awesome information.

[NeoPa]

We're here to help.

We generally like to help people to understand as that's far more use to them than simply fixing one simple problem.