Adding Daily Time On Duty In a Query

[RRLady]

I am having trouble with Daily Times entered in quarter hours adding up
correctly for my query.

We are collecting daily time spent on certain activities. Time can be
entered as whole hours or quarter hours. The field in the table is a Number
Data Type formatted as Single showing 2 decimal places.

There is another set of fields that show the Start Time for the day and the
End Time for the day. These fields are also Number Data Type formatted as
Integar, and are entered in a Military type format; i.e. 0800, 1430, 0545,
1315.

The Total Time Spent On Duty Activities MUST EQUAL the Total Hours Worked.

The Total Hours Worked is calculated by Subtracting the End Time from the
Start Time Then dividing by 100.

The problem is in how the data is entered.

If the quarter hours are entered in minutes (0.15, 0.30, 0.45) they add to
60 minutes which is an hour, but not to a whole number.

If the quarter hours are entered in decimal quarters (0.25, 0.50, 0.75) they
add to 100 which is a whole number, but not a whole hour.

Example:

Start Time - 0700
End Time - 1615
Total Hours Worked - 9 Hours 15 Minutes

Time spent on activities shows full hours for all but one which has 1 hour
15 minutes.

If you enter the time as 1.15 it will equal the Total Hours Worked; however,
when you add up the Full Weeks Time Spent on Activities, you will not get the
correct value.

How can I get around this problem?

Thanks for the help.

 

[aaron.kempf]

you should store everything as a DATE like NOW() and then you can do
dateDiff("H", dateStart, DateEnd) in order to calculate the number of
hours.

good luck

-Aaron

 

[RRLady]

If I store the time spent on activities as a Date field, how would we imput
easily imput the time for the activity. For example, if they worked 1 hour
15 minutes on a activity, they want to just enter 1.15 or 1.25. THey
absolutely do not want to have to enter the time as shown for a clock. The
field contains just the total time, not a TO and FROM clock time.

RRLady

 

[RRLady]

To further explain...

We are imputing the duration for the activiites.

I keep saying Time, but mean the duration of time spent on the activity.

I know that Access has trouble with Dates being used as Times. I was trying
to avoid that by using a Number data type, and not the Date data type.

I hope this helps ...LOL

RRLady


Thanks!

 

[John Vinson]

There is another set of fields that show the Start Time for the day and the
End Time for the day. These fields are also Number Data Type formatted as
Integar, and are entered in a Military type format; i.e. 0800, 1430, 0545,
1315.

Well... this WON'T WORK.

Numbers are in the decimal base system.

Times are in the old Babylonian hexadecimal (base 60) system.

1445 + 245 is equal to 1690, not to 1730. This is not an error - not
on Access' part anyway!!

I'd suggest storing your durations as Long Integer minutes. You can
display this as hhnn format using an expression

[duration] \ 60 & Format([duration] MOD 60, "00")

The \ is an integer division operator, not a typo.

For data entry you can use two adjacent unbound textboxes for hours
and minutes; in their AfterUpdate events, calculate the minutes and
store into an invisible bound textbox:

Me!txtDuration = 60*NZ(Me!txtHours) + NZ(Me!txtMinutes)

John W. Vinson[MVP]

 

[Apmarg]

I tried a method similar to this which works okay. I'm storing the time as a
decimal hours, ie 1:30 is 1.5. I have two adjacent fields on my input screen
one for hours and one for minutes that both calculate the recalculate the
result filed after update.

It works well enough, except that this is taking place on a detail line of a
form where there are multiple details. The two input fields are not bound,
so when I enter values into them it propagates to all the other visible
details. Only the single detail gets updated so it works, sort of, but is
there a way to only have the one detail show the values being input?

This may not be the best thread for this problem, but I thought I'd ask
anyway.

There is another set of fields that show the Start Time for the day and the
End Time for the day. These fields are also Number Data Type formatted as
Integar, and are entered in a Military type format; i.e. 0800, 1430, 0545,
1315.

Well... this WON'T WORK.

Numbers are in the decimal base system.

Times are in the old Babylonian hexadecimal (base 60) system.

1445 + 245 is equal to 1690, not to 1730. This is not an error - not
on Access' part anyway!!

I'd suggest storing your durations as Long Integer minutes. You can
display this as hhnn format using an expression

[duration] \ 60 & Format([duration] MOD 60, "00")

The \ is an integer division operator, not a typo.

For data entry you can use two adjacent unbound textboxes for hours
and minutes; in their AfterUpdate events, calculate the minutes and
store into an invisible bound textbox:

Me!txtDuration = 60*NZ(Me!txtHours) + NZ(Me!txtMinutes)

John W. Vinson[MVP]

 

[John Vinson]

I tried a method similar to this which works okay. I'm storing the time as a
decimal hours, ie 1:30 is 1.5. I have two adjacent fields on my input screen
one for hours and one for minutes that both calculate the recalculate the
result filed after update.

It works well enough, except that this is taking place on a detail line of a
form where there are multiple details. The two input fields are not bound,
so when I enter values into them it propagates to all the other visible
details. Only the single detail gets updated so it works, sort of, but is
there a way to only have the one detail show the values being input?

This may not be the best thread for this problem, but I thought I'd ask
anyway.

One sneaky way to do this is to use TWO textboxes carefully
superimposed upon one another. One would be bound, but also set Locked
= True and Enabled = False, Tab Stop set to No; the other, unbound,
would be enabled and have its tab stop property true.

When you're tabbing through the controls you would "pop" the unbound
textbox into focus, but normally it would be hidden behind the bound
control.

Unfortunately this doesn't work if the user is using the mouse to
focus on the textbox (though the technique is very handy if you need
an unbound combo box - just superimpose a textbox on the text area of
the combo leaving the downarrow visible).

John W. Vinson[MVP]