Date/Time Field

[martinw]

Field Question

I am designing a data base that keeps track of time spent on a particular project. I have set the field up formatted to HH:mm but Access wants to treat it as clock time (i.e. 1:30 pm) instead of 1 hour and thirty minutes. Where am I going wrong?? Thanks in advanced for your help.

 

[FoFa]

You are using a date field, and Access (all DB's for that matter) treat it as a time (not hours and minutes). A Date field contains a numeric counter of something (milliseconds, seconds, stc.) from some known point. The actual date/time is calculated from that number to it's value internally. Even though you setup the field as HH:MM, it is still holding a date/time, it only display's the time portion. What you set when you set it to HH:MM is an OUTPUT format (I.E. This is how I want to see the data even though you have a date and time internally). I would search the forums for the answer you seek since this has been discussed before.

 

[Paula_NZ]

Martin

For job costing hours worked do you need to log start and finish times or just the actual hours done??. Try and avoid setting up a time field unless you really understand this function, its confusing, dosent calculate well etc.

I use an access to track time to projects which in many cases is for billing information and not just jobcosting and what we have works really well

Select Employee Name (master form)

Date (default to today) Job No (picklist) ActivityCode (picklist) Hours (entry) Comments (optional text field) Chargeup (checkbox)


If more detail is required for say work to be billed stick a comment in the comment field "off site at 3pm" etc

If you also need to have start and finish times create a picklist that has time choices in half hour (or whatever) increments. Use this as a record rather than a base for calculation

 

[Pat Hartman]

Your data should be stored as minutes in an integer field. That way you can sum it and/or convert it to HH:MM as needed. All you need to remember is that there are 60 minutes in an hour and the mod function returns the remainder of a division operation.

If you search the archives you should find examples based on storing seconds and converting to days, hours, minutes, and seconds. I think this sample db includes a sample function:

Useful date calculation functions