Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Access 2000 - Calculate a Single Time Field

Posted on 2002-06-14
Medium Priority
252 Views
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

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
Question by:DWB
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 13
• 6
• 3
• +1

LVL 3

Expert Comment

ID: 7079207
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

Author Comment

ID: 7079218
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

LVL 1

Expert Comment

ID: 7079263
Are you putting the summation field on the footer of the subform or on the footer of the main form?
0

Author Comment

ID: 7079277
Footer of the Subform
0

LVL 1

Expert Comment

ID: 7079300
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

Author Comment

ID: 7079316
Yes I'm placing the summations in the form footer.

Name:  txtTotalHours
ControlSource: =Sum(Nz([txtTimeSpent],0))
Format: Short Time
0

Author Comment

ID: 7079326
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

LVL 3

Expert Comment

ID: 7079327
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

Author Comment

ID: 7079336
I just tried your solution, and unfortunatly I still receive the error message.  This is very frustrating :-(
0

Author Comment

ID: 7079343
Yes, just as written, I even just changed the name of the total field to "txtTotalTime" to reflect your suggestion.
0

Author Comment

ID: 7079351
The database is 859kb zipped, I can probably strip it down a little, if someone is willing to have a peak:-)
0

LVL 1

Expert Comment

ID: 7079352
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

LVL 3

Expert Comment

ID: 7079354
Well send me the Database with a few register and I search a solution if you want.....
0

Author Comment

ID: 7079357
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

LVL 5

Expert Comment

ID: 7079359
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

LVL 5

Expert Comment

ID: 7079360
Sorry, the paren is missplaced

=Sum(Cdate(Nz([txtTimeSpent],0)))
0

LVL 1

Expert Comment

ID: 7079363
If you want to send me your .mdb, you can send it to nhfeldman@hotmail.com
0

Author Comment

ID: 7079366
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

Author Comment

ID: 7079382
Still no luck, I've just sent the db off to nhfeldman.
0

LVL 1

Accepted Solution

n f earned 400 total points
ID: 7079434
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

LVL 1

Expert Comment

ID: 7079445
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

Author Comment

ID: 7079684
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.

0

Author Comment

ID: 7079787
Nancy,

Youâ€™re fantastic!!!!

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

Warmest Regards,

Dave
0

Author Comment

ID: 7079789
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filledâ€¦
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient â€¦
###### Suggested Courses
Course of the Month7 days, 19 hours left to enroll

#### 610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.