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.
good luck
-Aaron
RRLady
>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]
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!
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.
>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]