useing a date

[harleyken]

evaluations are due on a persons hire date. with 200 + personnel, i wanted
to build a reminder for myself to ask the supervisor for the evaluation....


I want to extract the hire month and day from: personnel.hiredate
and - add the current year to the date if system date is less than (m, d)
or add current year + 1 if system date is greater than (m, d). appending the
new date to the evaluationdue table..

 

[John Vinson]

evaluations are due on a persons hire date. with 200 + personnel, i wanted
to build a reminder for myself to ask the supervisor for the evaluation....


I want to extract the hire month and day from: personnel.hiredate
and - add the current year to the date if system date is less than (m, d)
or add current year + 1 if system date is greater than (m, d). appending the
new date to the evaluationdue table..

Suggestion:

It sometimes helps to explain *what you want to accomplish* rather
than insisting on a particular way of accomplishing it - in this case,
your suggested method is both difficult *and* unnecessary.

Unless you want the EvaluationDue date to be editable, there is
probably no need to store it in any table at all; it would certainly
NOT be typical to have an "evaluationdue" *TABLE*.

You can create a Query based on the Employee table with a calculated
field

DateSerial(Year(Date()) + IIF(Format(HireDate, "mmdd") >
Format(Date(), "mmdd"), 1, 0), Month([hiredate]), Day([hiredate]))

John W. Vinson[MVP]

 

[harleyken]

:

Suggestion:

It sometimes helps to explain *what you want to accomplish* rather
than insisting on a particular way of accomplishing it - in this case,
your suggested method is both difficult *and* unnecessary.

Unless you want the EvaluationDue date to be editable, there is
probably no need to store it in any table at all; it would certainly
NOT be typical to have an "evaluationdue" *TABLE*.

You can create a Query based on the Employee table with a calculated
field

DateSerial(Year(Date()) + IIF(Format(HireDate, "mmdd") >
Format(Date(), "mmdd"), 1, 0), Month([hiredate]), Day([hiredate]))

John W. Vinson[MVP]

Thanks for helping..

What I want to accomplish is: Evaluations are due on anniversary of hire
date. I want a reminder of those that are comming due(next 30 days or so),
and a seperate list of those past due..

 

[Rick B]

Then use the code he provided to put the employee's due date in a new column
in your query. Then, in the criteria for that column, put...

Between Date() and Date()+30

That would pull up all records where the due date was between now and 30
days from now.

For past due, use the criteria...
Between Date()-90 and Date()-1

That will pull all that are past due between one and 90 days.


--
Rick B

:

Suggestion:

It sometimes helps to explain *what you want to accomplish* rather
than insisting on a particular way of accomplishing it - in this case,
your suggested method is both difficult *and* unnecessary.

Unless you want the EvaluationDue date to be editable, there is
probably no need to store it in any table at all; it would certainly
NOT be typical to have an "evaluationdue" *TABLE*.

You can create a Query based on the Employee table with a calculated
field

DateSerial(Year(Date()) + IIF(Format(HireDate, "mmdd") >
Format(Date(), "mmdd"), 1, 0), Month([hiredate]), Day([hiredate]))

John W. Vinson[MVP]

Thanks for helping..

What I want to accomplish is: Evaluations are due on anniversary of hire
date. I want a reminder of those that are comming due(next 30 days or so),
and a seperate list of those past due..