Date differential between a full date and a date that has a year and month.

[scottypws]

Date Differential between full date (month, day, year) and partial date
(month and day).

Really need you help on this one.

I am trying to write a QBE expression that identifies if the an employee's
last performance evaluation date (e.g. 07/15/2007 a full date with month, day,
and year) is within one year of the employee's anniversary date of hire (e.g.
12/15 just the month and day--no year). If it is, the evaluation date will
be tagged as "current."

If the evaluation date (e.g. 11/10/2007)is within 60 days of the
anniversary month (e.g. 12/15) I will tag the evaluation date as "Pending."

And if the evaluation date (e.g. 10/20/07) is greater than 60 days from the
anniversary month (e.g. 07/25) than the evaluation will be tagged as "Overdue.
"

How can I use a DateDiff to express these conditions in a QBE expression?

Thanks,

Scotty

 

[Ken Snell \(MVP\)]

Perhaps this as the calculated field -- be sure to test thoroughly for all
your desired scenarios:

PerformanceReviewStatus:
IIf(DateDiff("d",[PerformanceEvaluationDateFieldName],
DateSerial(Year(Date())-(Format(Date(),"mmdd")>Replace([EmployeeAnniversaryDateField],
"/","",1,-1,1)), CLng(Left([EmployeeAnniversaryDateField],2)),
CLng(Right([EmployeeAnniversaryDateField],2))))<365, "Current",
IIf(Abs(DateDiff("d",[PerformanceEvaluationDateFieldName],
DateSerial(Year(Date())-(Format(Date(),"mmdd")>Replace([EmployeeAnniversaryDateField],
"/","",1,-1,1)), CLng(Left([EmployeeAnniversaryDateField],2)),
CLng(Right([EmployeeAnniversaryDateField],2)))))<=60, "Pending", "Overdue"))

 

[scottypws via AccessMonster.com]

Thanks for your prompt response. The expression took in the QBE; but
unfortunately, I acquired the infamous #Error output for the Current and
Pending conditions; but the default Overdue appeared.

Perhaps this as the calculated field -- be sure to test thoroughly for all
your desired scenarios:

PerformanceReviewStatus:
IIf(DateDiff("d",[PerformanceEvaluationDateFieldName],
DateSerial(Year(Date())-(Format(Date(),"mmdd")>Replace([EmployeeAnniversaryDateField],
"/","",1,-1,1)), CLng(Left([EmployeeAnniversaryDateField],2)),
CLng(Right([EmployeeAnniversaryDateField],2))))<365, "Current",
IIf(Abs(DateDiff("d",[PerformanceEvaluationDateFieldName],
DateSerial(Year(Date())-(Format(Date(),"mmdd")>Replace([EmployeeAnniversaryDateField],
"/","",1,-1,1)), CLng(Left([EmployeeAnniversaryDateField],2)),
CLng(Right([EmployeeAnniversaryDateField],2)))))<=60, "Pending", "Overdue"))

Date Differential between full date (month, day, year) and partial date
(month and day).

[quoted text clipped - 25 lines]

Scotty

 

[Ken Snell \(MVP\)]

I cannot reproduce the #Error result; double-check for typos, etc. Be sure
you changed all generic field names to your actual names.

I am not convinced that this expression will produce all the results that
you want -- primarily because the logic that you posted for how to decide
whether something is overdue or current or pending seems to be incomplete
with regard to deciding which "year" to attach to an anniversary date so
that the "days" comparison can be made properly. (For example, you said hat
an evaluation date within one year of an anniversary date maeans that the
evaluation is Current; yet, you then say if the dates are withing 60 days,
the evalatuion is either Pending or Overdue -- without apecific year to
attach to an anniversary date, this comparsion seems to be extraneous, so I
anticipate that you need to expand your logic to encompass all
possibilities.) Go through your decision logic and see if additional
criterioa can be provided to enhance this expression's usefulness, and then
we can assist in more detail.

It also would help if you post specific examples, using data values for the
two different fields, that will show how the logic is to be applied.
Remember, without a year value in the anniversary date field, the expressoin
will need to "assign" a year to the date in order to do a proper evaluation.
--

Ken Snell
<MS ACCESS MVP>

Thanks for your prompt response. The expression took in the QBE; but
unfortunately, I acquired the infamous #Error output for the Current and
Pending conditions; but the default Overdue appeared.

Perhaps this as the calculated field -- be sure to test thoroughly for all
your desired scenarios:

PerformanceReviewStatus:
IIf(DateDiff("d",[PerformanceEvaluationDateFieldName],
DateSerial(Year(Date())-(Format(Date(),"mmdd")>Replace([EmployeeAnniversaryDateField],
"/","",1,-1,1)), CLng(Left([EmployeeAnniversaryDateField],2)),
CLng(Right([EmployeeAnniversaryDateField],2))))<365, "Current",
IIf(Abs(DateDiff("d",[PerformanceEvaluationDateFieldName],
DateSerial(Year(Date())-(Format(Date(),"mmdd")>Replace([EmployeeAnniversaryDateField],
"/","",1,-1,1)), CLng(Left([EmployeeAnniversaryDateField],2)),
CLng(Right([EmployeeAnniversaryDateField],2)))))<=60, "Pending",
"Overdue"))

Date Differential between full date (month, day, year) and partial date
(month and day).

[quoted text clipped - 25 lines]

Scotty

 

[scottypws via AccessMonster.com]

Wow! You're right! Ididn't see the contradiction between pending and
current, unless I make Current 1 year minus 2 months prior to the Anniversary
Month and Day and retain Pending within 60 days prior to the Anniversary
Month and Day.
The issue you raise regarding which year has always been the lynchpin of the
problem. When a new employee enters our organization, the date of original
hire is titled "Anniversary Date" (which is a real contradiction to what an
anniversary date should be and is used to calculate retirement benefits).
Each year on the actual anniversary of the original "Anniversary Date , which
is static, we have to use the anniversary month and day as the basis to
calculate Current, Pending, and Overdue performance evaluations. So now you
see the crux of my problem. It would be neat if the personnel system
automatically updates an actual report due date based on the original
anniversary date. Perhaps what I can do is to create a field that switches
the original anniversary year to the next projected year and use that year as
the basis for the expression.

Anyway, I really appreciate your assistance and just by corresponding with
you gave me a little better understanding of what we are facing to produce an
working expression that meets our needs.

Thanks again,

Scotty
Human Resource Consultant 3

I cannot reproduce the #Error result; double-check for typos, etc. Be sure
you changed all generic field names to your actual names.

I am not convinced that this expression will produce all the results that
you want -- primarily because the logic that you posted for how to decide
whether something is overdue or current or pending seems to be incomplete
with regard to deciding which "year" to attach to an anniversary date so
that the "days" comparison can be made properly. (For example, you said hat
an evaluation date within one year of an anniversary date maeans that the
evaluation is Current; yet, you then say if the dates are withing 60 days,
the evalatuion is either Pending or Overdue -- without apecific year to
attach to an anniversary date, this comparsion seems to be extraneous, so I
anticipate that you need to expand your logic to encompass all
possibilities.) Go through your decision logic and see if additional
criterioa can be provided to enhance this expression's usefulness, and then
we can assist in more detail.

It also would help if you post specific examples, using data values for the
two different fields, that will show how the logic is to be applied.
Remember, without a year value in the anniversary date field, the expressoin
will need to "assign" a year to the date in order to do a proper evaluation.

Thanks for your prompt response. The expression took in the QBE; but
unfortunately, I acquired the infamous #Error output for the Current and

[quoted text clipped - 19 lines]

 

[Ken Snell \(MVP\)]

Try this -- I've modified the test for "current" status result:

PerformanceReviewStatus:
IIf(DateDiff("d",[PerformanceEvaluationDateFieldName],
DateSerial(Year(Date())-(Format(Date(),"mmdd")>Replace([EmployeeAnniversaryDateField],
"/","",1,-1,1)), CLng(Left([EmployeeAnniversaryDateField],2)),
CLng(Right([EmployeeAnniversaryDateField],2)))) Between 61 And 365,
"Current",
IIf(Abs(DateDiff("d",[PerformanceEvaluationDateFieldName],
DateSerial(Year(Date())-(Format(Date(),"mmdd")>Replace([EmployeeAnniversaryDateField],
"/","",1,-1,1)), CLng(Left([EmployeeAnniversaryDateField],2)),
CLng(Right([EmployeeAnniversaryDateField],2)))))<=60, "Pending", "Overdue"))

--

Ken Snell
<MS ACCESS MVP>

Wow! You're right! Ididn't see the contradiction between pending and
current, unless I make Current 1 year minus 2 months prior to the
Anniversary
Month and Day and retain Pending within 60 days prior to the Anniversary
Month and Day.
The issue you raise regarding which year has always been the lynchpin of
the
problem. When a new employee enters our organization, the date of
original
hire is titled "Anniversary Date" (which is a real contradiction to what
an
anniversary date should be and is used to calculate retirement benefits).
Each year on the actual anniversary of the original "Anniversary Date ,
which
is static, we have to use the anniversary month and day as the basis to
calculate Current, Pending, and Overdue performance evaluations. So now
you
see the crux of my problem. It would be neat if the personnel system
automatically updates an actual report due date based on the original
anniversary date. Perhaps what I can do is to create a field that
switches
the original anniversary year to the next projected year and use that year
as
the basis for the expression.

Anyway, I really appreciate your assistance and just by corresponding with
you gave me a little better understanding of what we are facing to produce
an
working expression that meets our needs.

Thanks again,

Scotty
Human Resource Consultant 3

I cannot reproduce the #Error result; double-check for typos, etc. Be sure
you changed all generic field names to your actual names.

I am not convinced that this expression will produce all the results that
you want -- primarily because the logic that you posted for how to decide
whether something is overdue or current or pending seems to be incomplete
with regard to deciding which "year" to attach to an anniversary date so
that the "days" comparison can be made properly. (For example, you said
hat
an evaluation date within one year of an anniversary date maeans that the
evaluation is Current; yet, you then say if the dates are withing 60 days,
the evalatuion is either Pending or Overdue -- without apecific year to
attach to an anniversary date, this comparsion seems to be extraneous, so
I
anticipate that you need to expand your logic to encompass all
possibilities.) Go through your decision logic and see if additional
criterioa can be provided to enhance this expression's usefulness, and
then
we can assist in more detail.

It also would help if you post specific examples, using data values for
the
two different fields, that will show how the logic is to be applied.
Remember, without a year value in the anniversary date field, the
expressoin
will need to "assign" a year to the date in order to do a proper
evaluation.

Thanks for your prompt response. The expression took in the QBE; but
unfortunately, I acquired the infamous #Error output for the Current and

[quoted text clipped - 19 lines]

Scotty