Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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