H
hughess7
Hi all
I have a YTD report which calculates working days in a month for each month
in a year per staff member (specialist). This result is used in further
calculations and is stored in an unbound text box called WJan, WFeb, WMar etc.
If a person leaves part way through a month I want the result to reflect the
number of days that were available to be worked for the period they were
still employed. I have created an extra query which calculates this value
based on a leave date, just for any staff that have left the company. I have
used dlookup on the report to display the value in an unbound text box called
LastMonth. I also have the leave date displayed in DateLeft. They are in the
Specialist footer and both are blank if the person is still employed.
The problem I have is how to use the value in LastMonth if one exists, in
place of the correct Month. eg one employee left 03/02/06 and currently WFeb
has 20 days stored. I want it to use the value in LastMonth instead which is
3 days.
WFeb already contains an IIF statement
=IIf(DatePart("m",Date())<3,Null,wDinM([Forms]![frm resource
menu]![txtYear],2))
The further calculation is a Productivity percentage field (SpecFeb being
the amount of days the Specialist has worked)
=IIf(DatePart("m",Date())<2,"",[SpecFeb]/[wFeb])
It should be 100% as they have worked 3 days out of an available 3 but
currently shows 24% as it is using the 20 days.
Hope this hasn't completely confused you! I might be approaching it in the
wrong way but if anyone can offer any advice I'd appreciate it.
Thanks in advance.
Sue
I have a YTD report which calculates working days in a month for each month
in a year per staff member (specialist). This result is used in further
calculations and is stored in an unbound text box called WJan, WFeb, WMar etc.
If a person leaves part way through a month I want the result to reflect the
number of days that were available to be worked for the period they were
still employed. I have created an extra query which calculates this value
based on a leave date, just for any staff that have left the company. I have
used dlookup on the report to display the value in an unbound text box called
LastMonth. I also have the leave date displayed in DateLeft. They are in the
Specialist footer and both are blank if the person is still employed.
The problem I have is how to use the value in LastMonth if one exists, in
place of the correct Month. eg one employee left 03/02/06 and currently WFeb
has 20 days stored. I want it to use the value in LastMonth instead which is
3 days.
WFeb already contains an IIF statement
=IIf(DatePart("m",Date())<3,Null,wDinM([Forms]![frm resource
menu]![txtYear],2))
The further calculation is a Productivity percentage field (SpecFeb being
the amount of days the Specialist has worked)
=IIf(DatePart("m",Date())<2,"",[SpecFeb]/[wFeb])
It should be 100% as they have worked 3 days out of an available 3 but
currently shows 24% as it is using the 20 days.
Hope this hasn't completely confused you! I might be approaching it in the
wrong way but if anyone can offer any advice I'd appreciate it.
Thanks in advance.
Sue