Need a 'smart' field

[doci4a]

Hi guys,

Here's what I'm trying to do...

I have a table of employees with their HIRING_DATE stored in a field in DATE format. In the same table I have another field which is called TRIAL_PERIOD which is in a NUMBER format (possible values 0,3,6,12 - 3,6,12 months of trial period respectivelly, and 0 if the trial period is over).

Where it all gets messy, is that I need to display the schedule for the evaluations (as a DATE):
FIRST_EVALUATION, SECOND_EVALUATION and THIRD_EVALUATION

The problem is that those dates are calculated differently for the different trial periods:
e.g. for a 3 months trial period the evaluations are done as follows:
1st eval - after 1 month
2nd eval - not required
3rd eval - after 2 months

for a 6 months trial the evals are:
1st eval - after 2 months
2nd eval - optional
3rd eval - after 4 months

and for a 12 months trial:
1st eval - after 2 months
2nd eval - after 6 months


3rd eval - after 10 months


What do you recon to be the best way to implement this?


Thanx

[pbaldy]

You can't have a calculated field, so you're going to need to set those values yourself. I'd probably have a function in the form where the user inputs the hire date and trial period. Use a Select/Case to evaluate the trial period, and within each case set the 3 dates appropriately.

[ssanfu]

Here's what I'm trying to do...

I have a table of employees with their HIRING_DATE stored in a field in DATE format. In the same table I have another field which is called TRIAL_PERIOD which is in a NUMBER format (possible values 0,3,6,12 - 3,6,12 months of trial period respectivelly, and 0 if the trial period is over).

Where it all gets messy, is that I need to display the schedule for the evaluations (as a DATE):
FIRST_EVALUATION, SECOND_EVALUATION and THIRD_EVALUATION

One way is to use a UDF in a query to calculate the dates "on-the-fly". (See attached A2K3 mdb; it could use more validation code).

If you want to store the dates so you can nudge the dates a little, put code in the after-update event of the two fields "HIRING_DATE" and "TRIAL_PERIOD", checking that both controls have valid data and if the data is good, calc the eval dates

or

use code in the form before-update event to validate the two controls, then use the UDF to calculate the 3 dates, then save the record. If you want to add an optional date or change the eval date a few days, you can edit the controls.

just my $0.02 .....

[doci4a]

That's exactly the kind of thing I needed. There's absolutely no problem in calculating the data on the fly as I won't be needing to save it somewhere. If can be generated every time the form is opened. I'll play around a little with your code and keep you posted.
Thx

[doci4a]

That's the ticket Works great! Thank you for your help

[ssanfu]

You're welcome.