Adding time values

stillnew2vb

Registered User.
Local time
Today, 00:05
Joined
Sep 20, 2010
Messages
34
Greetings Gurus,

Please could someone tell me how to add 2 time values together?

I have a form which has two unbound text boxes into which a user can submit a numeric value (e.g. "10") which is supposed to be the amount of time that user has been doing something. This value is supposed to be added to a date/time field on the same form but it just isn't happening..

My current "after-update" code
Code:
Dim dtDuration As Date
Dim dtMinute  As Date
Dim dtHour As Date

dtDuration = Me.Duration
dtMinute = Me.AddMins
dtHour = Me.AddHours

Me.Duration = Me.Duration.OldValue + dtMinute
The code doesn't error anywhere but the dtDuration field doesn't change, dtMinute & dtHour are being regarded as 0.
 
What does AddMins contain?? I assume something like 10.... for 10 minutes.

In this case you want to do 1 of 2 things... But first you need to know that date is actually a number
Today, 12:10 pm is rougly 40462.506759
where 40462 is today and the decimals indicate half a day (or 12 hours) have passed.
Knowing that...
1) Use dateadd function
Find it in the access help, read it, learn it, love it.

2) Decimal times
Converting the integer 10 to ten 'real' minutes.
Knowing that 0.5 is 12 hours....
One day is 24, so 12/24 == 0.5
Now 10 minutes, 60 minutes to an hour.... 10/60/24

So to change your 10 minutes value to 10 'real' minutes:
dtMinute = Me.AddMins / 60 / 24
 
Hi Mailman,

Nice to talk to you again...

That works in terms of actually getting a value out of dtAddMin, I think my problem is getting the right time formats as every result is coming up as a "long date"
 
if you just put a whole number 10 in any date field it will be treated as 10 days.

since a whole number of 1 equals 1 day, then it follows that

an hour = 1/24 (ie a 24th part of day)
a minute = 1/1440 (ie a 60th part of an hour)

access has lots of date and time management functions. you need to get used to them.

HOWEVER, if all your fields are to be treated as minutes, then just leave them as numbers, not dates - then you can add them, but in this case 20+30+35 will be 85 (minutes) and not 1hr 25 minutes without you doing some more work
 
Hi GTH,

That's not quite what I meant. The SQL backend's columns are formatted datetime so their display is "01/01/1900 03:26:16" where the actual duration
is 03:26:16

The plan is this:-
On my form I have an "opened" time e.g.08/10/2010 18:32:48
I also have a "closed" time e.g. 08/10/2010 18:33:14
I then have a "consumed" time which = "closed - opened" e.g. 00:00:26
I also have an "effort" time (in this case) 15mins e.g. 00:15:00
I then finally have a duration = effort + closed - opened e.g. 00:15:26

The problem however is three-fold:
1. If the duration exceeds 24 hours the time counter "resets" and instead of seeing 26:15:26 where the "case" has been opened for 1 day:2hrs: 15mins:26secs I see only 2hrs:15mins:26secs
2. If I want to add a "consumed" time I have to enter it in the 00:00:00 format which is prone to errors
3: looking at the table I see the duration as 04/01/1900 20:21:48 in some cases. I understand why this happens I am just not sure what to do about it.
 
I see

time on its own is always modulus 24hrs - as you have seen - so 13hrs plus 12hrs is 1 hr, and subtraction gives misleading answers also.

you possibly need to develop some routines to convert the time into integer minutes, (ie a ny fraction of a day will correspond to fraction x 1440 minutes - and then reconvert them back to get days/hours/minutes etc.

you only have to do it once, as it will be reusable afterwards!

you may not realise that MS implemented dates, based on a value for date 0 of 30/12/1899

so a date of 4/1/1900 corresponds to an integer value of 5 (days)

hence a perceived date of 4/01/1900 20:21:48 corresponds to 5 days 20 hrs etc

hope this makes sense
 
Access (natively) doesnt allow for a display of 26 hours, as Dave says, time is a modules 24 hours...

Oracle and other systems allow for 26 hour display format, but in access you need to make your own Function... Something along the lines of:
Code:
Public Function fnTimeOnly(YourDateTimeField As Date) As String
    fnTimeOnly = Int(YourDateTimeField) * 24 + DatePart("H", YourDateTimeField)
    fnTimeOnly = fnTimeOnly & ":" & Format(YourDateTimeField, "MM:SS")
End Function
Which might need some tweaking as this is aircode, also I am sure if you search the forum you will find other simular functions.

1 && 3. Is because of the 24 hour modules and will be fixed by above (or simular) function
2. If "effort" time is always in minutes you can enter it as just a plain old integer of 15.
Even if you want to denote seconds you can say 15.5 for 15 mins 30 secs
The only thing you then need do is refert it back to "part of a day" by deviding 15.5 by 60 (mins) then by 24 (hours)
?cdate(15.5 / 60 / 24)
12:15:30 AM

You can make your own logic what so ever, i.e. Have a user enter 3, when each "unit" is 5 minutes... meaning 15 minutes.

Have a user enter 0.15 for 15 minutes 1.15 for 1 hour, 15 minutes
Offcourse in this case 0.65 would be invalid or .... maybe equal to 1 hour 5 mins??

With some creativity you can rule the world with MSAccess, make it do what ever you want. :D
 

Users who are viewing this thread

Back
Top Bottom