Help - 3rd posting no answers - can it be done?

[Malcolm P]

Adding a calculated date to a group header

I have an employee time off data base. Time off is tracked using three
catagories: personal, sick and vacation days off.

We base an employee's annual time off on their employment year (start date
plus one year: eg, 7/25/2000 to 7/25/2001} vs. a calendar year. A formula
used in our reports which allows this is as follows:
shifteddateoff: [time off].dateoff+DateDiff("d",[employee.Start],"01/01/" &
DatePart("yyyy",[employee.START]))

Reports have the following layout:
LastName Header: shows name and start date
Shiteddateoff Header: shows the sum of time off for each employment year
with a label stating "Employment Year Total"
Type Header: shows the sum of time off for the employment year for each
category (personal, sick and vacation)

Question - Shiteddateoff Header section of report:
How can I incorporate a text box (?) in this section of the report which
will calculate and state the year end date? Example: if the employee
started 7/25/00, the first section should state 7/25/01, the second section
should state 7/25/02, etc.

 

[jl5000]

When working with fiscal periods is better to have a table that represents
them like:

Fiscal Year Fiscal Period Initial Date Final Date Description
------------ --------------- ------------ -------------
-------------
2001 1 07/25/2000 08/25/2000 Period 1
2001
....
2001 12 06/25/2001 07/24/2001 Period 12
2001

Then on your report's query refer to this table to to retreive the
description of the period, I recommend you do not change actual dates since
7/25/00 is not the same as 7/25/01, but if you refer to 7/25/00 as Period 1
of Fiscal Year 2001 that will help,

I believe your sections should be reflecting the fiscal periods rather than
"calculated" dates that do not reflect the real date,

 

[Malcolm P]

I'm not sure of how to implement your suggestion.

The following is an example of my report. How do I implement your suggestion?
Example:
Page 1

(LastName Header section)
Employee: Malcolm P Start Date: 7/25/00

(Shiteddateoff Header section)
Employment Year Total: 20 days

(Type Header section)
Personal 10 days
Sick 4 days
Vacation 6 days

(Shiteddateoff Header section)
Employment Year Total: 12 days

(Type Header section)
Personal 2 days
Sick 7 days
Vacation 3 days

(End Page 1)

The first Shiteddateoff Header section represents the first employment year
ended 7/25/01. How do I get the date 7/25/01 to show in this header section.
The second Shiteddateoff Header section represents the second employment
year ended 7/25/02. How do I get the date 7/25/02 to show in this header
section.
There are Shiteddateoff Header sections for each employment year: 7/25/03,
7/25/04, 7/25/05, etc.
Also, there are over 15 employees.

When working with fiscal periods is better to have a table that represents
them like:

Fiscal Year Fiscal Period Initial Date Final Date Description
------------ --------------- ------------ -------------
-------------
2001 1 07/25/2000 08/25/2000 Period 1
2001
...
2001 12 06/25/2001 07/24/2001 Period 12
2001

Then on your report's query refer to this table to to retreive the
description of the period, I recommend you do not change actual dates since
7/25/00 is not the same as 7/25/01, but if you refer to 7/25/00 as Period 1
of Fiscal Year 2001 that will help,

I believe your sections should be reflecting the fiscal periods rather than
"calculated" dates that do not reflect the real date,



--
jl5000
<a href="http://www.joshdev.com"></a>

Adding a calculated date to a group header

I have an employee time off data base. Time off is tracked using three
catagories: personal, sick and vacation days off.

We base an employee's annual time off on their employment year (start date
plus one year: eg, 7/25/2000 to 7/25/2001} vs. a calendar year. A formula
used in our reports which allows this is as follows:
shifteddateoff: [time off].dateoff+DateDiff("d",[employee.Start],"01/01/" &
DatePart("yyyy",[employee.START]))

Reports have the following layout:
LastName Header: shows name and start date
Shiteddateoff Header: shows the sum of time off for each employment year
with a label stating "Employment Year Total"
Type Header: shows the sum of time off for the employment year for each
category (personal, sick and vacation)

Question - Shiteddateoff Header section of report:
How can I incorporate a text box (?) in this section of the report which
will calculate and state the year end date? Example: if the employee
started 7/25/00, the first section should state 7/25/01, the second section
should state 7/25/02, etc.