Sum of Calculated Field in hours:minutes format?

[peter2012]

I have a table of timecards each recording minutes spent on each task for each client and I have summed those minutes by client in a query.

I have then in the same query converted each sum of minutes to hrs:mins format using this calculated field:

HrsMins: [SumOfMinTime]\60 & Format([SumOfMinTime] Mod 60,"\:00")

So this turns e.g. 261 minutes into 4:21

Based on that query I have a report showing total hrs:mins spent on each client, e.g.

Smith 4:21
Jones 5:32
James 1:23

Now I want to show a total at the bottom, i.e. in the above example it would be 11:16.

So for the control source for a total field on the report I tried:

=Sum([HrsMins])

But when I do that I get an error message:

"Data type mismatch in critieria expression"

I don't understand this.

I may be because I am trying to sum calculated fields amounts that are calculated using a function.

Or it may be that the format is not being recognised as hours and minutes and thus cannot be added up.

Can anyone think of a way in which I could get a total here in hours and minutes format (hrs:mins).

 

[bob fitz]

This is just a guess and is untested.
If you have a field in the query called [SumOfMinTime] perhaps you could make this the data source of a hidden text box. Then use something like
Sum([SumOfMinTime]\60 & Format([SumOfMinTime] Mod 60,"\:00"))
in a text box in the footer section.

 

[plog]

I vote a function in a module. Because you want this in various places you're going to save time and effort by making a function you can pass a value and have it return the formatted data as you like it instead of pasting your formatting code everywhere.

 

[peter2012]

Thanks Bob and Plog

I am still struggling as to what function to use, given the error message reported earlier.

In Excel I have no problem at all summing hrs:mins, by formatting the relevant cells [h]:mm, but there is no equivalent in Access so far as I can tell.

Peter

 

[plog]

Your error is based on the fact that you are trying to sum a text. [SumofMinTime] is a number, but [HrsMin] is text. So when you go to sum it, it has no idea how to==data type mismatch.

So, the function you need to use, is one that doesn't exist right now. You need to create a function in a module using the logic that produces your [HrsMin] field.

 

[The_Doc_Man]

In Access, times are actually DOUBLE format. (I.e. 64-bit REAL)

If you have a delta-time (i.e. the direct difference between two DATE fields) stored in a DATE format field, you really have a number that is a fraction of a day.

You can "roll your own output format" by doing something like

sMyString = Format$( [Datefield], "hhh:nn" )

Look up user-formatted date/time strings and realize that if you give it THREE "h" characters in the format, you can get elapsed hours/minutes using the format I showed.

However, if you have the difference in any other numeric format because you converted the times first, you cannot use what I just showed you. Then, you will need a custom subroutine/function to do that conversion. Function, if you need it in an output control such as a textbox - in which case you would do the data source as "=MyNewFunction(value)"