time format

[sadie]

I have a subform based on a table..
I want to enter the time spent with a client by minutes and/or hours
How do I format that in my table and subform

 

[Ally]

Because it's a calculated field you don't really want to store it in your table.

You can get the time difference by using the DateDiff funtion. You can use it in the query that feeds the form or via a textbox in the form itself.

Instead of a table to feed your form, make a query including all your other data and add:

For the hours:
DateDiff("h",[YourTimeArrived],[YourTimeFinished])

For the minutes:
Use "n" (not "m"!) instead of "h" in the function.

I'm not sure if there's a way of getting hours and minutes in one, but the way I've done it before is by doing two separate functions then concatenating them in a text box.

If you don't want to make a query, you can do this via text boxes, directly in the form just using the = sign before DateDiff, but you would need three, (one for hours, one for minutes, both invisible and the visible one to join the two together).

Hope this is clear - any more questions, post back.

 

[ansentry]

Time Format

Sadie,

Have a look at the attached sample DB.

Your can enter say 2:00 AM and the "Short Time" format will turn change it to 24 Hour clock format but it appears to calculate the time fine. I have not used it in a program, I just found it on the web somewhere.

As Ally said you don't want to store the answer in a form.

You will see that it is based on a query and the Answer is in the query.

I would suggest that you put 2 combo boxes in your subform and name them StartTime and EndTime and have the time in each in 15 minute Increments, worked on the 24 hour clock.


HTH


Regards,

John A

 

[sadie]

time

thanks for the responses, but can't I just enter the am't of time spent without calculating as there is a different am't of time spent on each record of the client in the subform
here are soem of my fields


Name work entry time spent $ charged

 

[Fizzio]

You can store this value, yes. As Ally and ansentry said, normally, you would not store this value. However, if you do not have a reference to calculate from ie start and end time, then you are not able to do the calculation.

How I would do this is to store the time spend with the client as a short time format ie hh:mm. and have a separate record for each consultation/visit. Yo can then use this function (modified from this microsoft page ) to calculate the total time.

Function GetTimeTotal ()

 Dim db As DAO.Database, rs As DAO.Recordset
 Dim totalhours As Long, totalminutes As Long
 Dim days As Long, hours As Long, minutes As Long
 Dim interval As Variant, j As Integer

 Set db = dbengine.workspaces(0).databases(0)
 Set rs = me.recordsetclone 'use this if you are calculating on a form
 Set rs = Set rs = db.OpenRecordset("YourClientRecordTable")
 rs.filter = "[i][ClientID][/i] = " & [i]Forms!frmClientInfo!ClientID[/i]
 set rs = rs.openrecordset
 interval = #12:00:00 AM#
    While Not rs.EOF
       interval = interval + rs![i][TimeField][/i]
       rs.MoveNext
    Wend
 totalhours = Int(CSng(interval * 24))
 totalminutes = Int(CSng(interval * 1440))
 hours = totalhours Mod 24
 minutes = totalminutes Mod 60

 GetTimeCardTotal = totalhours & " hours and " & minutes & " minutes"

 End Function

the bits marked in italics indicate items that you will need to change according to your own form / field names.

You can also look here for some more reading material.

hth