Converting minutes into hours in report

[jinro]

Hey there all,

I have a tracking database I have been working on and so close of being done just held up on this last part. What I am trying to do is have the totals = Ex. 1.25 + 3.50 would give Grand total of 5.15 not 4.75. The table has a field called time spent which stores the time. I have attached an example database. So whenever the minutes = more then 60 for it to add 1 to the hour.


Thanks for any help and your time.

[June7]

Are you saying that the 75 is 75 minutes and not .75 (45 minutes) of an hour? That really looks like decimal hours. How did you calculate the values of 1.25 and 3.50?

To convert decimal hours to HH:MM, try:

x represents the decimal hours value of 4.75

Int(x) & ":" & (x - Int(x)) * 60

I tried to open your db but Access 2007 does not recognize it. Must be a 2010 or 2013 version. If you still have issue with this calc, I can try with 2010 later.

[ssanfu]

Actually, the grand total would be 5:15 (5 hours and 15 minutes). 1.25 + 3.5 = 4.75 = 5.15

Here is a function to do the conversion:

Private Function Convert2HrsMIns(sHrsMin As Single) As String
   Dim x As Single
   Dim M As Single
   Dim rM As Single

   Dim H As Integer
   Dim rH As Integer

   x = sHrsMin   'orig number
   H = Int(x)  'hours part
   M = (x - H) * 100   '  minutes part
   rH = M \ 60  ' integer division - hours in the minutes
   rM = (M Mod 60) / 100   '  remainder (minutes)

   Convert2HrsMIns = H + rH + rM

End Function

[June7]

Not if the .75 is a decimal hour (i.e. 75/100ths of an hour) - 0.75 hour is 45 minutes.

[jinro]

Yes 5.15, ive been banging my head all day so miss typed. So that code I just paste it in the module then what?

[June7]

Call the function:

from query

CalcHours: Convert2HrsMIns([fieldname])

or

textbox:

=Convert2HrsMIns([fieldname])

[jinro]

When i try as query I get error undefined function "Convert2HrsMIns" in expression.
When I try as textbox It pops up a window box that says Convert2HrsMIns and is acting like it wants me to type something in, when i click nothing or even put something in it just says error.
I must be doing something wrong, i feel like im so close. Thanks for your guys help and time btw. trying to tinker with it still as we speak

btw i attached an .mdb version this time since you said you could not open the last one. Maybe if you saw what I am might help

[June7]

That file is named: Sample.mdb.accdb

Can't open that one either.

To use in query the function must be in a general module.

[jinro]

"smacks self on head" i should of had a V8... sorry had extensions turned off, this one is correct .mdb

[June7]

Sorry, Access 2007 still says unrecognized format. I will double check with 2010 later.

[jinro]

ok thanks, i attached one last time i just created a brand new access in .mdb on top it now says 2002-2003 file format. the other said 2007-2010. If you dont have time to try again i understand and I already appreciate all the time you have put in to this problem for me.

[jinro]

=/ if you were able to open it, it was messed up dragging the files right over messed it up.. attached is good ones had to rebuild the query pulling over killed it.

[June7]

You don't want to use it in that query because it is not summing the time data.

The function needs to be declared Public or leave off 'Public' and it will default as Public but 'Private' prevents other modules from seeing the function.

Then call the function in report textboxes:

=Convert2HrsMIns(Sum([time spent]))

[jinro]

YAYYYYYYY!!!!!!!!!!!! "bows and bows a zillion times"
thats it got it...THANK YOUS SOOOOOO MUCH, I CAN FINALLY SLEEP!!!! =) thank you sooo much