I'm sorry for having 2 different threads going. I'm going to restate what
I
wrote in the other one--I'm not sure how to stop that one, but I'll just
reply to this one. I have read your comments. I'm going to try the "D" &
Format(Day([Date]),"00") right now.
All of my fields are displaying [5:30]
I don't have any idea what type of data value is returned from the
HoursAndMinutes() function. I expect it is a string. You should be able
to
use a crosstab query creates column headings with and expression like:
ColHead: "D" & Format(Day([Date]),"00")
I will try this, but if I change I'm not sure how to get my reports to
work.
For March I need my report to show
1 2 3 4
Person1 5:30 4:30 4:00 3:30 (etc. for each day of March)
Person2 5:00 4:00 3:00 2:00
The crosstab would get rid of 31 expressions like "IIf([Day of Month],
...."
I was using [1] as a field to then use in my report for all the first day
total of each month.
All of my fields are displaying totals in this format [5:30]
Here's my reply in the other thread:
My input forms works great! I have a the standard Time In/Time Out
defaulting to start and end times. I have a checkbox if the person is
absent--making data entry as quick as possible. This Attendance Database
is
for my husband's nonprofit--they have to report so much information to
the
government. Now they want to know how much actual time is spent, and they
need to provide reports. People can leave for therapy/appointments, so I'm
recording areas if they leave (2 separate time in/time outs). It works out
great. Here are some fields from my query. (I used HoursjAndMinutes from
Elapsed time.) [Date] is the date of the day worked.
Total:
HoursAndMinutes(([TimeOut]-[TimeIn])-nz(([TimeOut2]-[TimeIn2]))-nz(([TimeOut3]-[TimeIn3])))
Total Time: IIf([Absent]=-1,0,[Total])
Day of Month: Day([Date])
1: IIf([Day of Month]=1,[Total Time])
I created a similar payroll report, and I was trying to duplicate what I'd
previous done. I sorted by name and hid the details. I'm trying to put a
formula in the name footer--I need to see each person and the total hours
they worked each day of the month.
Field Header:
Name 1 2 3 4 5 6 7 8
9 (etc.)
Footer Fields on my report:
name =nz([1]) =nz([2]) (etc.)
Of course that doesn't work. At this point I just want to add up all the
time in [1]. I'm not getting the difference of times. Is there a way of
getting one field [Total Time] to total? I thought it would be great if I
could convert [Total Time] back to a number so I could add it all up and
then
convert it back to display it as hours. I tried
TimeChange: CDbl([Total Time])
but that returned an error...
Well any help would be so greatly appreciated. Can I call Microsoft? I
know
they charge a lot, but I don't even know if they'd help with a report
design
question like this.
Duane Hookom said:
Did you read any of my comments and/or recommendations?
I don't have any idea what type of data value is returned from the
HoursAndMinutes() function. I expect it is a string. You should be able
to
use a crosstab query creates column headings with and expression like:
ColHead: "D" & Format(Day([Date]),"00")
The crosstab would get rid of 31 expressions like "IIf([Day of Month],
...."
I would strongly recommend displaying durations of time as the number of
minutes or hours. If someone asked you how many hours you worked today,
you
would never answer "eight o'clock" or "eight thirty". You might say
"eight
hours" or "eight and a half hours".
I would record the durations in a more normalized manner but you seem to
be
fairly comfortable with your current structure.
--
Duane Hookom
MS Access MVP
--
Rita said:
Here are some of my fields in my query:
Day of Month: Day([Date])
1: IIf([Day of Month]=1,[Total Time])
2: IIf([Day of Month]=2,[Total Time])
(same formula for all 31 days of the month)
Total:
HoursAndMinutes(([TimeOut]-[TimeIn])-nz(([TimeOut2]-[TimeIn2]))-nz(([TimeOut3]-[TimeIn3])))
Total Time: IIf([Absent]=-1,0,[Total])
I can see my 5:30 on the screen. My goal is to get a report to show
each
person's name and going across the days of the Month 1-31. For each
person
I
want each day's total hours worked. In my report I hide the details and
want
to create =nz([1]) in the footer to show
name 5:30 5:30 4:30 (name and total time for days 1-31 of month.
I want data entry to be quick and easy. I already have the form set up
and
it displays perfect! I show total hours in week--have start and end
times
defaulting. I have a check box if their absent.
THANKS FOR YOUR HELP!!!!!!
:
What and where is
1:Iif([Day of Month]=1,[Total Time])
1) if you had pasted the actual expression, I would have expected to
see
IIf
not Iif.
2) IIf() expects 3 arguments and you have only 2.
3) if [Total Time] and [1] are expressions in the same SQL/query, I
would
not use the calculated column in another calculation.
4) I always use 2 arguments in Nz() and it uses ()s not [ ]s so the
correct
expression might be:
=Sum(Nz([1],0))
I think using Nz() in Sum() shouldn't be necessary
Maybe if you just gave us a few sample records with the desired
calculations
and results.
--
Duane Hookom
MS Access MVP
--
I just posted, but wanted to explain my problem in more detail. I
used
the
HoursAndMinutes from Elapsed time. I have [Time in] and [Time out]
but
I
also
have options for up to 2 additional [Time in] and [Time out] that I
need
to
also track. SO the total hours in a day become burdensome. I want
data
entry
to be easy so if they're absent, it's just a checkbox. I got it all
to
figure
out hours and it's in a field [Total].
This is to check if absent: Total Time:Iif([Absent]=-1,0,[Total])
Then I need to have a total hours appear on a monthly report showing
how
many hours each person worked
This is my formula if... 1:Iif([Day of Month]=1,[Total Time])
THAT WORDS...
My problem now is getting it to print on my report. I'm hiding the
details
(grouped by name). I've done similar reports, but all with numbers.
My
formula in the footer would normally be =sum(nz[1]) BUT THAT
DOESN'T
WORK
BECAUSE IT'S TIME. I tried =sum(HoursAndMinutes(nz[1]) that doesn't
work...
I have my totals in a field for day 1 in 1, totals for 2nd day of
month
is
in field [2]
Then I thought about converting [Total Time] to a number to get it
to
total
and then converting it back to time.
I'm so frustrated because I see my total in a the query, I just need
to
get
it to display.
I'D REALLY APPRECIATE ANY HELP!!!!!!!!
