473,378 Members | 1,527 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

Sum of short time values in report footer

24 16bit
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
Aug 6 '21 #1
5 3843
32,556 Expert Mod 16PB
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.
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.
Aug 6 '21 #2
24 16bit
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
Expand|Select|Wrap|Line Numbers
  1. Public Function toseconds(sDate As String) As Long
  3.     Dim hours           As String
  4.     Dim minutes         As String
  6.     hours = Trim(Split(sDate, ":")(0))
  7.     minutes = Trim(Split(sDate, ":")(1))
  9.     toseconds = CLng(hours) * 3600 + CLng(minutes) * 60 
  11. End Function
And the function used to convert seconds back to hh:nn is below
Expand|Select|Wrap|Line Numbers
  1. Public Function totime(dblSeconds As Double) As String
  3.     Dim hours           As Long
  4.     Dim minutes         As Long
  5.     Dim dUsedSeconds    As Long
  7.     Dim sHours          As String
  8.     Dim sMinutes        As String
  10.     dUsedSeconds = CLng(dblSeconds)
  12.     hours = Int(dUsedSeconds / 3600)
  13.     minutes = Int((dUsedSeconds Mod 3600) / 60)
  16.     sHours = Format(hours, "00")
  17.     sMinutes = Format(minutes, "00")
  20.     totime = sHours & ":" & sMinutes 
  22. 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
Aug 7 '21 #3
32,556 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. Public Function ToSeconds(sDate As Date) As Long
  2.     ToSeconds = CLng(sDate * 86400&)
  3. End Function
To convert back is equally simple :
Expand|Select|Wrap|Line Numbers
  1. Public Function ToTime(lngSeconds As Long) As Date
  2.     ToTime = CDate(lngSeconds / 86400#)
  3. End Function
Formatting such a returned value as HH:mm (Hours:Minutes) is as simple as :
Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 8 '21 #4
1 Bit
thanks for the awesome information.
Aug 16 '21 #5
32,556 Expert Mod 16PB
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.
Aug 16 '21 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Galina | last post by:
Hello I have a report, which lists records. Each record has money paid field. Money paid can be 0 or not 0. I calculate and print summary of money for a group in the group footer, as well as...
by: Mike MacSween | last post by:
I have a report based on: PARAMETERS Forms!frmProductions!ProdID Long; SELECT Sum(qryRoleEvent.Fee) AS SumOfFee, First(qryMusician.LastName) AS LastName, First(qryMusician.FirstName) AS...
by: SJM | last post by:
I have a report that displays records of real estate properties. It is possible for each property to appear a number of times for various reasons. Each record however is unique. What I would like...
by: jean.ulrich | last post by:
Hi I have a table with 4 fields Customer, Product, Price and Ares As I want to create a report using sorting and grouping I put Customer and I included group header and group footer then i...
by: leemansiuk | last post by:
hi all, i am quite new to access and have come stuck on quite a simple problem (i think). i have created a report which list items from an orders table etc.. as the report could grow (listing...
by: Greg | last post by:
Most suggestions on this topic recommend to use a page footer and make it visible only on the last page. My problem is that the footer is half of the height of a page which means the detail would...
D Giles
by: D Giles | last post by:
Access 2003: A subreport control (sum total calculated textbox located in the subreport report footer) does not show total of all records when referenced as a total in the main report footer - only...
by: JeffP | last post by:
Have a textbox in a report footer that sums data using Sum. That text box value is used in a few calculations in the footer. Trouble is, if the report has no data then the box simply shows #error...
by: adsaca | last post by:
there basic sections in Crystal report namely Report Header Page Header Group Header Details
by: DanicaDear | last post by:
I have been trying to do for two hours now what I *thought* would be a simple task. Can someone point me in a better direction? I have a report that shows which customers are due up for an order....
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.