• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

Access 2000 - Calculate a Single Time Field

I have a single time field on my subform, and wish to total all the time's entered, to produce a total for the Hours/Minutes spent working.

Sub-Sub-form: sfsubWorkPerformed
Field Name: txtTimeSpent
Type: Date/Time
Format: Short Time
Input Mask: 00:00;0;_

Example:

12:00  (12 Hours)
00:15  (15 Minutes)
01:36  (1 Hours 36 minutes)
00:12  (12 minutes)
======  
14:03  (14 Hours 3 minutes)
0
DWB
Asked:
DWB
  • 13
  • 6
  • 3
  • +1
1 Solution
 
marcoszorrillaCommented:
Open the foot of this form and drop a Textbox and in the control source write:

=Sum([MyField])

This text box will be formated as short time too.

Best Regards
Marcos.
0
 
DWBAuthor Commented:
Thanks for your respone!

I tried the same option earlier today, and I just retried, and I still get an error in the field "#Error".

I even tried the following: =Sum(Nz([txtTimeSpent],0))

I would appreciate any further guidance you could offer!

Cheers,

Dave
0
 
n fCommented:
Are you putting the summation field on the footer of the subform or on the footer of the main form?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
DWBAuthor Commented:
Footer of the Subform
0
 
n fCommented:
Assuming that the text box you are entering time into is called txtTimeSpent and the text box that shows the total time spent is called txtTotalTime,  create the following code:

Option Compare Database
Option Explicit

Dim OrigTime As Date
Dim TimeTotal As Date

Private Sub txtTimeSpent_AfterUpdate()
  TimeTotal = TimeTotal + Me.txtTimeSpent
  TimeTotal = DateAdd("n", OrigTime, TimeTotal)
  Me.txtTotalTime = TimeTotal
End Sub

Private Sub txtTimeSpent_BeforeUpdate(Cancel As Integer)
  OrigTime = Me.txtTimeSpent.OldValue
End Sub

You can then add a button, btnClearTime, with the code:
Private Sub btnClearTime_Click()
  OrigTime = 0
  TimeTotal = 0
  Me.txtTotalTime = 0
  Me.txtTimeSpent = 0
End Sub

to clear out the variables and the text boxes.
0
 
DWBAuthor Commented:
Yes I'm placing the summations in the form footer.

Name:  txtTotalHours
ControlSource: =Sum(Nz([txtTimeSpent],0))
Format: Short Time
0
 
DWBAuthor Commented:
Sorry about the last message, I had a this page did not refresh properly, and I ended up resending the message. However, I'll try your solution above.
0
 
marcoszorrillaCommented:
The code works fine as DWb and NFeldman are saying, are you sure that your are putting the textbox in the footer of the Subform and not in the main form.

And what about of the name of this field are you sure that is exactly as you write.

Best Regards.
Marcos
0
 
DWBAuthor Commented:
I just tried your solution, and unfortunatly I still receive the error message.  This is very frustrating :-(
0
 
DWBAuthor Commented:
Yes, just as written, I even just changed the name of the total field to "txtTotalTime" to reflect your suggestion.
0
 
DWBAuthor Commented:
The database is 859kb zipped, I can probably strip it down a little, if someone is willing to have a peak:-)
0
 
n fCommented:
Does the "#Error" message show up when you first open the form or after you have entered data into txtTimeSpent?

Also, if you use the code I suggested, you have to take the "Sum(Nz([txtTimeSpent],0))" out of the Control Source.  I see #Error whenever I have that in the Control Source.
0
 
marcoszorrillaCommented:
Well send me the Database with a few register and I search a solution if you want.....
0
 
DWBAuthor Commented:
I just removed the "Sum(Nz([txtTimeSpent],0))" and I lost the #error but and now view 0:00, but it's still not summing properly.

I will send if you provide an email address.
0
 
bob_onlineCommented:
The problem is that event though they're formatted as time, access is considering them text and you can't sum text fields.

Take this approach

ControlSource: =Sum(Cdate((Nz([txtTimeSpent],0))) to explicitly convert the value to a datetime data type.
0
 
bob_onlineCommented:
Sorry, the paren is missplaced

=Sum(Cdate(Nz([txtTimeSpent],0)))
0
 
n fCommented:
If you want to send me your .mdb, you can send it to nhfeldman@hotmail.com
0
 
DWBAuthor Commented:
I placed your suggestion into the ControlSource, and unfortunatly it did not work.  When I press the Clear Time button it clears my first entry on the subform, and if I don't press it, it will sum the first entry only.
0
 
DWBAuthor Commented:
Still no luck, I've just sent the db off to nhfeldman.
0
 
n fCommented:
DWB,

The problem was that txtTimeSpent was a bound field, and was bound to a field called "TimeSpent".  Simply putting in a =Sum([TimeSpent]) into the control source was enough.  What you are really trying to do is sum the field in the underlying table, not an unbound field.  I've rezipped your file and sent it back to you.

Also, since these are all bound fields, the "Clear Time" button becomes unnecessary, so I've removed it.

Nancy
0
 
n fCommented:
You may actually want to change the control source of txtTotalTime to

=Sum(nz([TimeSpent]))

to deal with null entries in the TimeSpent field.

Nancy
0
 
DWBAuthor Commented:
Nancy,

I've not received the file yet, however, when I do I 'll respond. I have to go out for an hour, and will check upon my return.

Thanks in advance.
0
 
DWBAuthor Commented:
Nancy,

You’re fantastic!!!!

Thank you very much; I truly appreciate your time and effort.

Warmest Regards,

Dave
0
 
DWBAuthor Commented:
I wish to also thank all Experts who took the time to try and help me out, your patience and guidance is, and has been greatly received and appreciated.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 13
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now