I am trying to create a feilds in a report that calculate time.

[MountainMan1]

I am trying to create a feilds in a report that calculate time but access only sees time in a 24 hour format so I am having trouble getting hours to calculate past 24. If anyone can help please do.

 

[GingerR]

please provide an example of what you are looking for.

 

[MountainMan1]

If I have 38 hours and 30 min and 40 hours and 20 min how do I get the report to show 78 hours and 50 min. Thank you for any help you can give.

 

[GingerR]

is the 38 hours and 30 mins in a field? how is it formatted? or is it in two fields (hours and mins)?

 

[MountainMan1]

The hours and min would be in one field. The fields are in the report and are formated with hh:nn:ss.

 

[GingerR]

are the hours/mins calculated within the report? or are they data within for example a query or table that the report is built upon?

 

[MountainMan1]

the hours/mins are comming from a query that is based on a table that was imported from excel and are calculated up in the report.

 

[GingerR]

so in the query, what exactly does the data look like? not the 'format' of it, but what is it? integer? all minutes but then formatted as hh:mm? i need to understand more, sorry for all the questions. but i cannot add something with an 'h' and an 'm' in it, so i'm trying to see what your number actually is without the formatting.

what is your statement in your query?
what is any expression you have in your report so far?

 

[MountainMan1]

No problem asking questions, I just appreciate you trying to help. This is what the information would look like in access:
38:30:00
40:20:00
--------
78:50:00 <------ this is what i would like the output to be
but access is not giving this number becuase it only goes up to 24 hours below are some actual numbers that i'm working with. i just want the times to add up and show in a cell in the report.

Total Log In Hrs
36:27:32
18:29:06
37:38:01
26:35:26
34:33:26
30:02:20
26:10:30

 

[GingerR]

so is this what the data looks like in the table that you import from excel? if not, what does the orig data look like?

or is this what it looks like after you run your query?

 

[MountainMan1]

Yes this is what the original data looks like.

 

[GingerR]

and what it the format of it? sorry i just dont get it--
is the format of the orig data field date/time? or text?

i guess i'm trying to figure out why, if access only allows 24 hours, your orig data is more than 24 hours and it's formatting that correctly? that makes me believe that it is not a date/time format, but if it's text, it won't add cause it can only add things that are not text. anyhow, sorry again--i'm not slow or lame. just trying to figure out what you got going here. have you successfully added these numbers together already? where? what is your expression?
what does your query do? does it alter this field at all?
what is going on in your report? do you just have them summed in the footer?

 

[GingerR]

on second thought--how big is your db? can you compact it and zip it and send it to me? then i wont keep going back and forth with so many questions. you dont have to if you dont want to. datachick10@hotmail.com

 

[MichaelRed]

The (source) table
TaskTime
36:27:32
18:29:06
37:38:01
26:35:26
34:33:26
30:02:20
26:10:30

FirstQuery

SELECT tblTaskTime.TaskTime, basTxt2Intgr([TaskTime],"hh") AS MyHr, basTxt2Intgr([TaskTime],"nn") AS MyMin, basTxt2Intgr([TaskTime],"ss") AS MySec
FROM tblTaskTime
WITH OWNERACCESS OPTION;

The Function used to seperate the elementss

Public Function basTxt2Intgr(strTime As String, strPart As String) As Integer

    Dim MyAry As Variant
    MyAry = Split(strTime, ":")

    Select Case strPart

        Case Is = "hh"
            basTxt2Intgr = MyAry(0)

        Case Is = "nn"
            basTxt2Intgr = MyAry(1)

        Case Is = "ss"
            basTxt2Intgr = MyAry(2)

    End Select

End Function

First Query Results
TaskTime MyHr MyMin MySec
36:27:32 36 27 32
18:29:06 18 29 6
37:38:01 37 38 1
26:35:26 26 35 26
34:33:26 34 33 26
30:02:20 30 2 20
26:10:30 26 10 30


Second Query
SELECT Sum(qryTxtTime2Num.MyHr) AS MyHrs, Sum(qryTxtTime2Num.MyMin) AS MyMins, Sum(qryTxtTime2Num.MySec) AS MySesc
FROM qryTxtTime2Num
WITH OWNERACCESS OPTION;

Second Query Results
MyHrs MyMins MySesc
207 174 141

Procedure to return final Resuls

Public Function basHrMinSec2StrTime(MyHrs As Long, MyMins As Long, MySecs As Long) As String

    Dim lgSec As Integer
    Dim lgMin As Long
    Dim lgHr As Long

    Dim lgCary As Long

    lgSec = MySecs Mod 60
    lgCary = Int((MySecs - (MySecs Mod 60)) / 60)

    lgMin = (MyMins + lgCary)
    lgCary = Int((lgMin - (lgMin Mod 60)) / 60)
    lgMin = lgMin - (60 * lgCary)

    lgHr = MyHrs + lgCary

    basHrMinSec2StrTime = CStr(lgHr) & ":" & CStr(lgMin) & ":" & CStr(lgSec)

End Function

Of course, ALL of the above is more of an illustration' of the various steps to taks and lack even rudimentary error checking and is totally NOT integrated. The point is specifically to just show AN approcah with the seperate steps set out.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places