Duane,
I'm trying two different things here- one with the
underlying form and one with the report.
I added an unbound field (ShiftHours)to my (sub)form so
that I could see the hours per shift (the diff between
ShiftStartTime and ShiftEndTime). This sub(form) is in
datasheet view. I have 3 shifts: 11AM to 12PM, 12PM to
1PM, and 1PM to 1:30PM. Using either version of DateDiff,
each line on the subform shows 2.5 hours so ShiftHours is
calculating the total shift hours, not showing 1, 1, and
0.5 hours accordingly. I'm hoping you can address a forms
issue as well- how can I fix this?
If someone entered in a volunteer opportunity and forgot
to include the shifts, I would expect to have 0 ShiftHours.
Now onto the report. I added an unbound field using your
DateDiff for ShiftHours. This works correctly because
there is no datasheet view issue and I'm able to correctly
sum the ShiftHours by member. It evens handles the 1/2
hours- thanks! So even with my (sub)form problem, the
report should work. Except I still can't get the grand
total.
I have unbound field ShiftHours in the detail sction as
=(DateDiff("n",[ShiftStartTime],[ShiftEndTime]))/60
I have unbound field ServiceHours as
=Sum((DateDiff("n",[ShiftStartTime],[ShiftEndTime]))/60)
Both work correctly.
So if I create unbound field TotalServiceHours would it
really be the same as ServiceHours, just in the page
footer? That doesn't seem to do the trick- anything I put
in the page footer comes up with "#Error".
Sorry it got so complicated- I appreciate your help!
Thanks, Stephanie
-----Original Message-----
Are there any fields with no values? Are both fields date/time data types?
If you have no values for a field, what would you expect to see as a result?
--
Duane Hookom
MS Access MVP
--
Thanks for the reply.
I tried the statement
=Sum(DateDiff("n",ShiftStartTime, ShiftEndTime))/60
in my Shifts form first and it didn't calculate correctly
for whole hours or for 1/2 hours (11AM to 1PM was "3").
Strange. I do like the idea however. If the shift were
12PM to 12:30PM, should it show "1.5"? Any idea why this
isn't working correctly?
I did try the statement in the report footer following
your steps and received the same output: "#Error". Any
suggestions?
Thanks,
Stephanie
-----Original Message-----
You should not need running sum. Also, I would choose a
smaller time
increment to handle 1/2 hours etc.
=Sum(DateDiff("n",ShiftStartTime, ShiftEndTime))/60
Make sure you place this expression in the Report Footer
and the name of the
text box can't be the name of a field.
--
Duane Hookom
MS Access MVP
--
in message
Hi. I have a report: page header, Member Name header,
AnimalName header (each Member can volunteer alone or
with
one of many animals), detail and page footer.
Members can volunteer for shifts that have
ShiftStartTime
and ShiftEndTime. I have a calculated field for Shift
hours:
=Sum(DateDiff("h",[ShiftStartTime],[ShiftEndTime]))
to determine the lenth of each shift and then total them
so that I know how many hours a volunteer has given.
This
is in the Member Name header.
Lastly and what I can't figure out, I want to have a
grand
total so that I know how many hours the organization has
given. I tried to put the same calculated field as
above
in the footer with properties set to RunningSum = yes
but
the output is "#Error". I know I'm not suppose to store
caculated fields, but it's tempting when having report
trauma. Any suggestions? Thanks,Stephanie
.
.
