Solved How to add fields with the Short Time format?

[Ole]

Hi
I have a time report which contains a field formatted as Short Time.
In the report there is a field for each day in the month.
I need to make a Sum of the field, so I can se the total amount of time spend for the month.

But it seems like the format "Short Time" can't chow more than XX:XX not f.ex. XXX:XX.

This is what I tried:
=Sum([ArbTid])
The field "ArbTid" is formatted as Short Time as well as the "Sum"-field.

Any ideas?

 

[arnelgp]

you can use Tempvars for your Total time?

use this expression as ControlSource of your textbox (sum):

=[Tempvars]![TotalTime]

then you add this code to the Open Event of your report:

Private Sub Report_Open(Cancel As Integer)

    Dim TotalMinutes As Double
    Dim hours As Long, minutes As Long
    Dim db As DAO.Database
    
    Set db = CurrentDb
    With db.OpenRecordset(Me.RecordSource)
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            If IsDate(!ArbTId) Then
                TotalMinutes = TotalMinutes + (Hour(!ArbTId) * 60) + Minute(!ArbTId)
            End If
            .MoveNext
        Loop
        .Close
    End With
    Set db = Nothing
        
    hours = Int(TotalMinutes / 60)
    minutes = TotalMinutes Mod 60
    
    TempVars("TotalTime") = hours & ":" & Format(minutes, "00")
    
End Sub

 

[Ole]

Thank you for helping me.
I do get an error highlighting the "With db.OpenRecordset(Me.RecordSource)"

 

[arnelgp]

what is your recordsource? query that ask a date range?
i cannot help if i can't see your report.

 

[Ole]

This is my Report - named "Timereport":

This is the Query named "Timereport":

I hope this helps

 

[arnelgp]

do you show your report in Report view or just Print preview?

 

[Ole]

Print preview. But it doesn't really matter how it's shown.

 

[arnelgp]

i asked because the code might not run on Report view.
add this to the Detail and Report Footer sections:

Option Compare Database
Option Explicit


Dim bolSecondTime As Boolean
Dim TotalMinutes As Double

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If Not bolSecondTime Then
            If IsDate(Me!ArbTId) Then
                TotalMinutes = TotalMinutes + (Hour(Me!ArbTId) * 60) + Minute(Me!ArbTId)
            End If
    Else
        TempVars("TotalTime") = Int(TotalMinutes / 60) & ":" & Format(TotalMinutes Mod 60, "00")
    End If
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
    bolSecondTime = True
End Sub

 

[Ole]

It seems to be working, but I get no result in the "Sum" field ("TotalTime").

The variable you'er using TempVars("TotalTime") is automatically changed to MidlertidigeVariabler("TotalTime").
Maybe this is causing some problems.

This is how the code looks like now:

 

[arnelgp]

can you use this as Control|Source:

=TempVars("TotalTime")

 

[arnelgp]

forget the Tempvar, remove the ControlSource of the TotalTime textbox (make it unbound).
now change the Format event of the Detail section:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If Not bolSecondTime Then
            If IsDate(Me!ArbTId) Then
                TotalMinutes = TotalMinutes + (Hour(Me!ArbTId) * 60) + Minute(Me!ArbTId)
            End If
    Else
        Me.TotalTime = Int(TotalMinutes / 60) & ":" & Format(TotalMinutes Mod 60, "00")
    End If
End Sub

 

[Ole]

I got no error, but the result is the same. No data are shown in the field "TotalTime".
Could it be because the field "TotalTime" not are in the ReportFooter, but in "Personaledata.Navnfod"?

 

[DHookom]

The root problem is using a date/time field to store a duration. D/t fields should only be used to store a point in time and numeric double (or similar) should be used for durations. When you enter 6:00 for six hours, the value saved in your table is 0.25 (1/4 of a day).

You can Sum() your durations but then multiply by 24 to convert to hours since their are 24 hours in a day. No VBA required.

 

[Ole]

The root problem is using a date/time field to store a duration. D/t fields should only be used to store a point in time and numeric double (or similar) should be used for durations. When you enter 6:00 for six hours, the value saved in your table is 0.25 (1/4 of a day).

You can Sum() your durations but then multiply by 24 to convert to hours since their are 24 hours in a day. No VBA required.

This woked! But not with format xxx:xx like Time: Minute

 

[DHookom]

You can create an expression (or find one on the web) to change the format. Personally, I much prefer to see 123.4 hours.

 

[Ole]

You can create an expression (or find one on the web) to change the format. Personally, I much prefer to see 123.4 hours.

I don't min seeing it as 123,4 hours. But to see it as 75,93 is a bit confusing (the xx,93 part)

 

[Pat Hartman]

But it seems like the format "Short Time" can't chow more than XX:XX not f.ex. XXX:XX.

1. Formatting a datetime field has ZERO impact on the stored value.
2. The stored value is a double precision number with the integer portion being the number of days since 12/30/1899 and the decimal portion is the percentage of a day that has elapsed. So the value 1.75 = Dec 31, 1899 06:00 PM.
3. The datetime field is always a POINT IN TIME. It is never elapsed time.
4. Therefore, you cannot use a datetime field and expect to format it to show elapsed time UNLESS the elapsed time will ALWAYS be < 24 hours.

You need to do the formatting yourself. Here's a sample database that may give you some ideas.

Sample Date Functions

This database contains a number of date functions that you can simply copy into your application. There is a tabbed form to show you how to use the calculations. A partial list of functions is: Business days between two dates. Business day + days = next business date Easter (Western) Age...

www.access-programmers.co.uk

 

[arnelgp]

ou can Sum() your durations but then multiply by 24 to convert to hours since their are 24 hours in a day. No VBA required.

this will work if your time exceed or equal 24 hours. what if it is not. multiplying by 24 certainly will render a wrong computation.

 

[arnelgp]

I got no error, but the result is the same. No data are shown in the field "TotalTime".
Could it be because the field "TotalTime" not are in the ReportFooter, but in "Personaledata.Navnfod"?

here is a demo with some vba (but not on the report).
see Query1 first and check out "TotalMinutes" calculated column.
next view Table1 report in design view and analyze the design.
next check out the function fnConvertToHrMin() in Module1.
this function is being used in the Table1 Report.
now open Table1 report in Print preview.

 

[DHookom]

this will work if your time exceed or equal 24 hours. what if it is not. multiplying by 24 certainly will render a wrong computation.

Multiplying by 24 will always work with the result being hours. That’s how durations work.