Solved

Access 2000 - Calculate a Single Time Field

Posted on 2002-06-14
24
243 Views
Last Modified: 2006-11-17
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
Comment
Question by:DWB
  • 13
  • 6
  • 3
  • +1
24 Comments
 
LVL 3

Expert Comment

by:marcoszorrilla
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

by:DWB
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

by:n f
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

by:DWB
ID: 7079277
Footer of the Subform
0
 
LVL 1

Expert Comment

by:n f
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

by:DWB
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

by:DWB
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

by:marcoszorrilla
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

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

Author Comment

by:DWB
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

by:DWB
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

by:n f
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 3

Expert Comment

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

Author Comment

by:DWB
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

by:bob_online
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

by:bob_online
ID: 7079360
Sorry, the paren is missplaced

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

Expert Comment

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

Author Comment

by:DWB
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

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

Accepted Solution

by:
n f earned 100 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

by:n f
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

by:DWB
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.

Thanks in advance.
0
 

Author Comment

by:DWB
ID: 7079787
Nancy,

You’re fantastic!!!!

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

Warmest Regards,

Dave
0
 

Author Comment

by:DWB
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now