SUM time from other fields, insert into Totals formatted as hurs.

[Guest]

I have a Table that has these filed formatted as number
1. TravelTime
2. PreperationTime
3. WorkTime
4. Daily Hours
I am looking for code that would SUM TravelTime, PreperationTime and
WorkTime and insert them into DailyHours On After update event of each of
1st, 2nd and 3rd filed and formatted as Hours.Minutes.

I’ve managed to get this close but not formatted as hours.minutes
This is what I did:
On After updated of 1st, 2nd and 3rd filed I wrote this code:
Daily Hours = TravelTime + PreperationTime + WorkTime
The problem with this is that instead of displaying 2.10 it displays 1.70.
Please assist?
Adnan

 

[Guest]

1. You should not be storing the Daily Hours in the table. Instead you should
be calculating it needed in queries, forms, and reports.

2. You say it should show 2.10, but displays 1.70. What does? What numbers
did you plug into the other three fields?

 

[Guest]

All four fields are formatted as number (#,##0.00) and with decimal places 2
I’ve created a form with record source of this table with these fields.
Now, I want the calculation to be performed on form. i.e: txtTravelTime’s
on After update.
Whatever formula/code it may be, it has to be in VBA so I can use on after
update (on form)
If this isn’t clean enough I would get you snapshot somewhow!
Thank you Jerry!
Adnan

 

[Guest]

Jerry --- this should make myself clearer on what I’m looking for:
http://punaime.itgo.com/msaccess1.html
Once again, thank you!
Adnan

 

[JK]

Adnan,

Access keeps time as numbers whereas 1=day, thus your input is 0.1 day
(2h-24m) +0.5 day (12h-00m) + 1.1 day (1d-2h-24m) =1.7 days (1d-16h-48m).

It appears that you meant 0.1=10m + 0.5=50m + 1.10=1h-10m (sum=2.1 =
2h-10m).

In order to get the correct result :

Specify the *all* the fields as date/time fields with a time format (hh:nn
and Decimal place =Auto) When you sum them also into a date/time field
(daily Hours) you will get the correct result. This will work fine
*provided that* you *never* exceed 23h=59m in daily hours (or any other
date/time field with time format for that matter).

Bear in mind the when you enter time only Access will default to day 0 (Dec
30,1899) and when the sum exceeds 23h-59m the actual number that is stored
is on Dec 31 1899. e.g
if the total is say, 26h-20m the actual result you will see is 01:20 and not
26:20 because Access will store Dec 31, 1899 01:20, Jan 1, 1900 for times in
excess of 47h-59m
etc.

Regards/JK

 

[Guest]

This is almost exactly what I’m looking for --- the only issue is this: “if
the total is say, 26h-20m the actual result you will see is 01:20 and not
26:20 because Access will store Dec 31, 1899 01:20, Jan 1, 1900 for times in
excess of 47h-59m etc.â€

How do I make this exceed 24h:20m --- or will there be any other solution?
Thank you, Adnan

 

[Guest]

....anyone, anything, please?

 

[JK]

Adnan,

The only way I know, other here may know better, is to write a function that
take the number and return a string - make the total (or any field that may
exceed 23h:59m) invisible and display the string generated by the function.

Regards /JK

This is almost exactly what I'm looking for --- the only issue is this:
"if
the total is say, 26h-20m the actual result you will see is 01:20 and not
26:20 because Access will store Dec 31, 1899 01:20, Jan 1, 1900 for times
in
excess of 47h-59m etc."

How do I make this exceed 24h:20m --- or will there be any other solution?
Thank you, Adnan