Help ACCESS working with time

[ceccoCSC]

Hi does anybody know how to sum the time in access?
It happens that if I go over the 24 hrs access returns only the remainder of it I.E:
8 hrs x 4 days = 32 hrs access shows 8

Thanks in advance for any help

 

[KenReay]

Hi

Depending on what you are trying to do the function DateAdd(interval, number, date) may prove useful.

But judging from your example (8hrs * 4days = 32hrs), you need to think about what you are trying to do, your example is only correct under certain conditions why is 768(hrs) not correct ie 8hrs * 4 * 24hrs), there are after all 24 hours in a day. To do calculations as in the example yopu quoted you should convert to the lowest common unit (say minutes) and just treat the minutes as a number (eg long of double, whatever), then convert back to Hr:mm (by division) for printing purposes.

Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[xlbo]

Set your time format to [h]:mm:ss
You are probably using hh:mm:ss in which case 32 hrs equates to 1Day and 8 hours ie in dd hh:mm:ss format, it would show 1 08:00:00
by using [h], it allows numbers greater then the intrinsic maximum to be shown
HTH Rgds
~Geoff~

 

[ceccoCSC]

Thanks both of you. However Ken I'm not a programmer so it would be a bit complicate for me to apply what you suggest. Xlbo it doesn't work. It works for excel but it doesn't in Access. Any other Idea?

Cheers

 

[KenReay]

Hi

You do not explain exactly waht you are trying to do, but I Suspect there is nothing very complicated.

From you description I suspect you have a start time and and end time for a number of days and you want to find the total time worked or whatever.

For each day get the elapsed time in minutes using DateDiff function eg

lngMinutes = DateDiff("n", StartTime, EndTime)

Now multiply this by the number if days eg

lngMinutes = lngMinutes * intDays

Where intdays is defined as on integer, not a date/time

Now to get back to hours and minutes

intHours = lngMinutes / 60

intMinutes = lngMinutes MOD 60

Is this what you are trying to do?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[ceccoCSC]

Ken
Well actually no. I don't have a start time and a end time. I already know how many hours the user is logged in.
what i'm trying to do is to have a table where I'll input the time in this format:
I.E.
Monday 07:31:45
Tuesday 07:35:21
Wednesday 07:33:10

and so on 'till Friday.
The problem comes when I sum them up using a query with the function SUM([Stats].[LoginTime]) using the parameters Start date and End date.
Let's say I want to know the total login time for the period between the 27/08/02 and 31/08/02.
The query is designed to ask for Start date and End Date and it works, it shows me all the records between the 2 dates but the Total time is not correct.
I can send you an example if you want.

Thanks for all the help you are providing I really appreciate it.

 

[KenReay]

Hi

No need to send anything, I get the idea, what I am saying is amend your table to hold the logged in time as a number of minutes (or seconds if you need that level of accuracy), You can then sum them quite nicely because they are just a number, but to print or display them you use the divide and mod operators from my earlier post to convert to days, hours, whatever.

Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use