Summing calculated fields on a form

[Jeff H]

[I tried to attach a sample database (400kb) with this post, but it's not accepting .accdb files. Here's a link to it on One Drive.]

With the help you gave me on the little Videos db, Hans, and the Michael Alexander book you recommended, I’ve been able to make some good progress converting my Hospice db from Excel to Access.

Right now I’m stuck on how to deal with time, specifically the duration of service events. Each Service Event counts as one visit but one visit can include many Services Provided, so I’ve set up a separate table to record the individual Services with their Durations.

In the Service entry form, the Services Provided will be a sub form of the Service Event main form. These durations have to be summed by Service Event and any other filter that may be applied. In the attached db, I’ve come close to an acceptable solution, but I need some help.

This sample data is a collection of volunteers who reported multiple services in one visit. The table includes a field called Duration which I manually populated in the table. I have an unbound control in the form header that adds up the Duration controls and works fine when filtered. I also added a Duration2 field to the table hoping to populate it on the form with the calculation fields -- but I couldn't see how to do that.

In operation, the users will enter either TimeIn and TimeOut as time or Hours and Minutes as integers. In either case, the data entered is converted to minutes and then to hours as a decimal and I want that decimal saved into the Duration field of the table.

SO HERE'S THE ISSUE: In testing the various methods for input, I can’t get a sum of my calculated decimal figures (controls “CalcTimeDecimal” or “CalcTimeDecimal2”). I just get an error. In fact, if I add an unbound control with =SUM([CalcTimeDecimal]), I get an error in that control and also in the existing "=SUM([Duration])" control.

Sorry for the verbosity! But I hope it makes sense.

[HansV]

I will look at it a bit later.

(For the future: to attach a database, zip it and attach the zip file to your post)

[Jeff H]

Hmm. Yeah I forgot that bit about zipping it.

[Jeff H]

Hans, the last thing I want to do is waste your time. I continued looking online and I think I found the answer:

I can use VBA in the After_Update events of the four fields that accept the data (TimeIn, TimeOut, Hours, and Minutes). In each case, if the both controls in the paired set are populated, I can reset the other set of paired controls to Null and add the calculated hours in decimal to the Duration field.

I don't know if this is the best way to do it, and if you want to look anyway and comment further I'd appreciate it. But I think this will solve my issue.

Thanks,
- Jeff

[HansV]

As you have found, you cannot sum controls that contain a calculated expression. Instead, sum the calculations themselves.

For the sum of CalcTimeDecimal:

=Sum(DateDiff("n",[TimeIn],[TimeOut]))/60

The expression in TimeEntry can be simplified to

=Nz([Hours],0)*60+[Minutes]

and the sum of CalcTimeDecimal2 then becomes

=Sum(Nz([Hours],0)*60+[Minutes])/60

or a bit shorter

=Sum(Nz([Hours],0)+[Minutes]/60)

See the attached version.

Dbs.zip

[Jeff H]

Excellent!! Many thanks. Little by little it seems to be making sense.

Since I these calculations are not just for the form I want them stored in the table so I put your formulas into the After_Update events for all four time entry controls and update the table with them. After that I should have the table values for any subsequent lookup or summing. Plus I now see how to do calculations in a form

Here are the 2 basic versions of the VBA I used. So far they are testing good.

Code: Select all

Private Sub Hours_AfterUpdate()

If Not IsNull(Me.Minutes) Then

    Me.TimeIn = Null
    Me.TimeOut = Null
    
    Me.Duration = (Nz(Me.Hours, 0) * 60 + Me.Minutes) / 60
    
End If

End Sub

and

Code: Select all

Private Sub TimeIn_AfterUpdate()

If Not IsNull(Me.TimeOut) Then
    
    Me.Hours = Null
    Me.Minutes = Null

    Me.Duration = DateDiff("n", Me.TimeIn, Me.TimeOut) / 60

End If

End Sub

- Jeff