Timesheet

[Billiam]

Ok, I got this to work....forgot I needed to put the =sign in front of the
expression.

Indeed it now gives hours for the start/finish/lunch calculation, and when
that isnt used and the Othertime column is used, it produces the time in
minutes (480 for an 8 hour day). I tried putting in /60 at the Othertime end,
which worked, but then the expression stopped taking the Lunchtime amount
away...I have been playing with this and tryng all sorts of possibilities,
but none seem to produce both situations in hours.
Billiam

Well, the calculation is giving you a POINT IN TIME. Summing points in time
gives you another point in time which includes a date and a time on that date.

If you want to sum DURATIONS of time, you should be calculating the time
worked in some type of units - minutes or hours and adding that to get a duration.

You were better off with the expression that returned 8 or 8.5 hours.

IF (repeat if) you use other time exclusively when it has a value, then this
expression will give you minutes. You can sum this to get a total number of
minutes.
IIF([OtherTime]>0,DateDiff("n",0,[OtherTime]),
(DateDiff("n",[StartTime],[FinishTime]) - Int([LunchTime] * 1440))/60 )

Then you can divide the results of that expression by 60 to get
hours and fractions of hours.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

So I was able to get everything working. I also added an afterupdate event
for the form (me.refresh) to get the unbound textbox expression to
show/calculate.

I also have a field called OtherTime, Date/Time, short time format, which I
use as a combobox (uses same ltable like Lunch), to enter in 8 hours (for a
sick day, a vacation day or Stat holiday--our company just pays 8 hours...no
calculation required).

The problem is, that start time finsih time and lunch time do not come into
play now, and so the expression is not working since those values are Null.
It will of course add anything fromthe othertime cbo to the total hours
calculated from starttimefinishtime-lunch, but it will not work when I only
select that time from the othertime cbo)

I expect I need to include an evaluation for Nulls in any of those
fields...could you help me as I cannot seem to get it correct?

The expression I am using in the unbound textbox is:

=([OtherTime])+DateAdd("n",-Int([LunchTime]*1440),DateAdd("n",DateDiff("n",[StartTime],[FinishTime]),0))

And then finally, the daily hours are recorded in the timesheet table, per
day, per workcode (Regular hours are paid at a certain rate by the bookeeper
when she gets the exported timesheet report...which I have to design, still,
and IT hours are paid at a different rate, and so they are a possible work
code, too.) in the field TotalDailyHours.

I have been trying to sum these in a query by workcodetype, but I cannot get
the values to display...is it because they are in shorttime format?

Just try it and see if it works. If it fails, post back with how it fails.

And if you wish to STORE the value then bind the comboboxes to the appropriate
fields in your timesheet table.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Billiam wrote:
Thank you, John. It makes sense to me as the time is unique to use it as the
pprimary key and bound field. I assume once i set it up that way, that the
time value chosen in the combobox will be picked up in the unbound textbox
expression, or do I need to do something in the afterupdate event to pass
that value to the unbound expression?

Thank you again for your patience and help...I do sincerely appreciate it!
Warmest Regards,

Billiam