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
Solved

Access 2000 - Calculate a Single Time Field

Posted on 2002-06-14
24
247 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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
 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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.

808 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