Calculate and store future date in a table

[Guest]

I have a DB that records staff appraisals. When an appraisal is added, I need
to calculate a review date 6 months after the actual appraisal date.

I have a text field in a form with the following:

=IIf([Actual Date] Is Not Null,DateSerial(Year([Actual Date]),Month([Actual
Date])+6,Day([Actual Date])))

Which works in the form, but I have just noticed it does not write the
review date to the table, so I can't use queries to find out when reviews are
due.

So, I need for the table to be able to create and store this value for me
after and actual date has been added into the record.

Any help greatly appreciated!

 

[Stefan Hoffmann]

hi Jennie,

Which works in the form, but I have just noticed it does not write the
review date to the table, so I can't use queries to find out when reviews are
due.

Use the BeforeUpdate event of the form, to assign your date.


mfG
--> stefan <--

 

[Guest]

Thanks for your quick reply, but could you give me a little more info please?
Is it the 'before update' for the form or the review date text box and what
do I put as the event procedure?

Thanks again.

 

[Douglas J. Steele]

Why bother storing it if it's always 6 months after the actual appraisal
date?

As fellow MVP John Vinson likes to say "Storing derived data such as this in
your table accomplishes three things: it wastes disk space; it wastes time
(almost any calculation will be MUCH faster than a disk fetch); and most
importantly, it risks data corruption. If one of the underlying fields is
subsequently edited, you will have data in your table WHICH IS WRONG, and no
automatic way to detect that fact."

SImply add a computed field in a query that figures out the date, and use
the query wherever you would otherwise have used the table.

 

[Jamie Collins]

Why bother storing it if it's always 6 months after the actual appraisal date?

I think the interval of six months is an approximation that would vary
for occurrence.

Such a meeting often needs to be formalized: a room and other
resources booked out for the duration, attendees' Outlook calendars
updated, etc. The calculation may be more involved: must be a weekday,
availability of aforementioned resource and attendees, etc. IMO, the
calculation

timestamp of last meeting add interval 15,897,600 seconds

is unrealistically crude.

As fellow MVP John Vinson likes to say "
"...If one of the underlying fields is
subsequently edited, you will have data in your table WHICH IS WRONG, and no
automatic way to detect that fact."

The timestamp of a past event is usually immutable <g>.

I don't know whether the OP wants to differentiate between reoccurring
events due e.g.

base date [e.g. hire date] add (interval six months * (number of
actual reviews + 1))

and their actual scheduling; further, whether only entering them when
they have actually occurred or future planned events too. If my pay
review is on 1 August and said to recur every six months but the first
meeting is postponed by six weeks due scheduling issues, I would still
expect my next review to become due in the following February rather
than the March.

Jamie.

--

 

[Bob Quintal]

=?Utf-8?B?amVubmllYmVudGhhbQ==?=

I have a DB that records staff appraisals. When an appraisal
is added, I need to calculate a review date 6 months after the
actual appraisal date.

I have a text field in a form with the following:

=IIf([Actual Date] Is Not Null,DateSerial(Year([Actual
Date]),Month([Actual Date])+6,Day([Actual Date])))

Which works in the form, but I have just noticed it does not
write the review date to the table, so I can't use queries to
find out when reviews are due.

So, I need for the table to be able to create and store this
value for me after and actual date has been added into the
record.

Any help greatly appreciated!

your code will mess up if you have an [actual date] in July
through December.

Better to use the datediff() function.
=dateAdd("M",6[actual date]

To get it to write to the table, instead of typing it in the
field, bind the textbox to the field in the table, put
the calculation in the Actual_Date_AfterUpdate event, and put
the name of the destination control before the equal sign.

 

[BruceM]

The DateSerial thing would be a problem for half the year, wouldn't it?
Just a syntax/typo note: I'm sure you intended to say "Better to use the
DateAdd() function", which is the one you demonstrated. Also, there is a
comma after the number, and a close parentheses:
=DateAdd("m",6,[actual date])

=?Utf-8?B?amVubmllYmVudGhhbQ==?=

I have a DB that records staff appraisals. When an appraisal
is added, I need to calculate a review date 6 months after the
actual appraisal date.

I have a text field in a form with the following:

=IIf([Actual Date] Is Not Null,DateSerial(Year([Actual
Date]),Month([Actual Date])+6,Day([Actual Date])))

Which works in the form, but I have just noticed it does not
write the review date to the table, so I can't use queries to
find out when reviews are due.

So, I need for the table to be able to create and store this
value for me after and actual date has been added into the
record.

Any help greatly appreciated!

your code will mess up if you have an [actual date] in July
through December.

Better to use the datediff() function.
=dateAdd("M",6[actual date]

To get it to write to the table, instead of typing it in the
field, bind the textbox to the field in the table, put
the calculation in the Actual_Date_AfterUpdate event, and put
the name of the destination control before the equal sign.

 

[Rick Brandt]

your code will mess up if you have an [actual date] in July
through December.

Why? DateSerial() properly handles all "spill-over" issues. If the month
exceeds 12 the year is incremented. If the day exceeds 31 the month is
incremented, etc.. The same applies with negative values. The appropriate date
portion is decremented to handle a negative value in either the month or day
arguments.

 

[Rick Brandt]

The DateSerial thing would be a problem for half the year, wouldn't it?

Nope. Try it and see what you get.

 

[Jamie Collins]

DateSerial() properly handles all "spill-over" issues.

All?

SELECT DATESERIAL(2 ^ 32, 1, 1)

generates an Overflow error.

I'm always being told that DATETIME values are double floating point
numerics and

SELECT TYPENAME(2 ^ 32)

returns 'Double'

Jamie.

--

 

[Rick Brandt]

All?

SELECT DATESERIAL(2 ^ 32, 1, 1)

generates an Overflow error.

I'm always being told that DATETIME values are double floating point
numerics and

SELECT TYPENAME(2 ^ 32)

returns 'Double'

Jamie.

But the arguments for DateSerial() are Integers with an additional
restriction on the year argument.

(from 97 help file)
The DateSerial function syntax has these named arguments:

Part Description
-------- ---------------
year Required; Integer. Number between 100 and 9999,
inclusive, or a numeric
expression.
month Required; Integer. Any numeric expression.
day Required; Integer. Any numeric expression.

 

[Jamie Collins]

But the arguments for DateSerial() are Integers with an additional
restriction on the year argument.

You're right! I should have said

All?

SELECT DATESERIAL(9999, CLNG(2^31 - 1), 1)

generates an Overflow error.

Jamie.

--

 

[BruceM]

Huh! I could have sworn I had run into a problem with that at some point.
I should have checked before posting. DateAdd is simpler to write, which I
guess is the only real argument for it in this case.

 

[Rick Brandt]

Huh! I could have sworn I had run into a problem with that at some
point. I should have checked before posting. DateAdd is simpler to
write, which I guess is the only real argument for it in this case.

There are subtle difference on month boundaries.
EX:
?dateadd("m",1,#1/31/07#)
2/28/07

?DateSerial(Year(#1/31/07#), month(#1/31/07#)+1, day(#1/31/07#))
3/3/07

So given the last day of a longer month DateAdd("m"... takes you to the
last day of the result month even when it is shorter. DateSerial on the
other hand adds the month and then the extra days of the starting month
causes it to wrap into the following month compared to DateAdd().

Which behavior is better would depend on the situation.

 

[BruceM]

OK, that's good to know. Thanks.

There are subtle difference on month boundaries.
EX:
?dateadd("m",1,#1/31/07#)
2/28/07

?DateSerial(Year(#1/31/07#), month(#1/31/07#)+1, day(#1/31/07#))
3/3/07

So given the last day of a longer month DateAdd("m"... takes you to the
last day of the result month even when it is shorter. DateSerial on the
other hand adds the month and then the extra days of the starting month
causes it to wrap into the following month compared to DateAdd().

Which behavior is better would depend on the situation.