Full Version: calculating total time spent within one field
UtterAccess Forums > Microsoft® Access > Access Date + Time
im a fairly new user to microsoft access.
ithin our database, we have a field for case notes where each case worker puts in notes about a specific case. i want to be able to allow each case worker to put in how much time they spent on each case.
there are multiple case workers that work on each case, and there may be multiple time entries, so i also want to able to calculate the total time each individual spent on each case.
if you need any more info, lemme know.
First create a reference to Microsoft DAO 3.51 Object Library.......
Insert this function into a module.

Function GetTimeTotal(tblName As String, fldName As String) as String

Dim db As DAO.Database, rs As DAO.Recordset
Dim totalhours As Long, totalminutes As Long
Dim hours As Long, minutes As Long
Dim varTime As Variant

Set db = DBEngine.workspaces(0).databases(0)
Set rs = db.OpenRecordset(tblName)
varTime = #12:00:00 AM#
While Not rs.EOF
varTime = varTime + rs(fldName)
totalhours = Int(CSng(varTime * 24))
totalminutes = Int(CSng(varTime * 1440))
hours = totalhours Mod 24
minutes = totalminutes Mod 60

GetTimeTotal = hours & " hours and " & minutes & " minutes"

End Function

......there =) , now you can call this function anytime you like.

To use the function would be
Dim strTotalTime as String

strTotalTime = GetTimeTotal("table name here", "field name here")

HAs for calculating each individual, one way would be to make a create table query and populate a temp table with filtered results... run the function on it..... delete temporary table =) Good Luck
thanks a lot for your help. sorry for the late reply.
ut, um, i am fairly new at microsoft access, so i need a little clarification.
Oput in that formula as a module, and i named the module as object library (it did not let me name it microsoft DAO..).
i am unsure of what i need to do next.
"Dim strTotalTime as String"
..i am not sure what it means.
i also was wondering if you could explain to me a little how i would make a query to list each individual.
First how to add a reference...
pen your database switch to code view by pressing alt + F11. At the top off the screen you should see a button named Tools click on this Tools button and then click on Refrerences from the drop down menu. The References window should now open. Scroll down until you see Microsoft DAO 3.51 Object Library. Check the square to the Left of it and click the OK button. You have now added a reference to Microsoft DAO 3.51 Object Library.
It doesn't really matter what you name the module as long as the code is exactly as I posted earlier. This is what you should paste into the Module...

To call this function you could create a button on a form and put this code in its OnClick event
Odon't know the size of your database but if it's not too big you could attach a copy of it and I could help you a little more.
I can't write exactly what you need because I don't know what your working with.
FOr you could e-mail it to me wirecook@hotmail.com
Do some searches on query tutorials or if I see what your working with I could help you make a query.
hope this helps =)
ok well i put in the code for object library ( i didnt have Microsoft DAO 3.51 Object Library, but i did have Microsoft DAO 3.6 Object Library, so i jst checked that).
also put in the command button, and put in the code for the OnClick event. however, whne i click on that button, it only says
"Run-time error '13'
type mismatch"
if i go to debug, it highlights this for me..
varTime = varTime + rs(fldName)
I am going to try to get u a copy of the database (either up on here or in email) in a little time (i have ot change some of the data for confidentiality issues).
i also have another issue with which maybe u could help me. it is under the Access Forms Forums under the subject, doing a grand total of totals from subforms onto the main form .
this is a copy of the database so that anyone can please help me.
Don't know if you got any further assistance with this, but ...
took a look at your mdb and there's only one table, so I'm assuming that "Victim Information" is the table you're using for the "table name here" part of the expression. Also, I'm guessing that you were using "Entry Date" as the "field name here" because this field contains null (empty) values, which would cause this error. That being said, the code will need some way to deal with those nulls. Maybe you could [1]make the field required or [2]set the Default Value so they're never null. The module could use some error/null handling but changing the table properties might suite your needs easier.
[1] Do this in the design view of the table. You'll see Required in the Field Properties pane - the default is no. Be sure you key in the missing dates into the table before you make this change.
[2] Also in the table design, also in the properties pane. Just put Date() in the Default Value if todays date is an acceptable entry, or Time() if the current system time is acceptable. Heck, you could even do both if you want: Date() & " " & Time(). You'll still need to go back and fill in the nulls for this to work as expected.
[2] ... umm, and there's also the Now() function, which does the same thing as Date() & " " & Time() with less typing. Love it when stuff like that occurs to me AFTER I've completed teh first post.
- Josh
Looking at your example, I would also take another look at the structure of your DB.
For example... all the information relating to the victim should be in a victim table. The same goes for the offender information.
All this information can then be pulled together via an Incident table which will link the Victim and Offender together.
If I get some time, I'll see if I can pull together an example for you thumbup.gif
Sorry bout the late reply.. was on a business trip.
ooked at your database the error is caused by using the notes field. The function assumes a time value and is getting alot of extra information.
I'll make a few adjustments to your database and upload it here.
sorry again for the long wait.
Well sorry for double post but unable to edit previous post... shrug.gif

anyway heres a modified version of the database you uploaded. I made quite a few changes to it. Hopefully it will get you started in the right direction.

The form "Case" is all you need to open others are just subforms.

Good luck with your project.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.