Date Calculations

I need some help setting up some date caluclations.

Fields Include:
A. DOB [date field] "date of birth"
B. IFSPOrginal [date field] "Date IFSP is entered"
C. IFSPRedo [date field] "Date 12 months into the future from
IFSPOrginal"
D. 2yrTransistion [date field] "Date 2yrs into the futre from DOB"

I'm having difficulty creating the expressions for the (C)IFSPRedo and
(D)2yrTransistion fields.

I have come up with the following possible expressions:
IFSPRedo Expression = me!IFSPRedo.value=dateadd("m",12,[IFSPOrignal])
2yrTransition Expression =
me!2yrTransistion.value=dateadd("m",24,[DOB])

3 Questions
Are these 2 Expressions correct?

Which field to you put it in?

Where do you write it? In an event?

re: Date Calculations

We don't store calculated fields in tables...instead, we just the calculated
control you made everytime we need to show it to the user (Forms, Reports)

You calculations will work just fine.

One more thing: If these "audits" change, or you need to add more audits, you
may want to consider making an Audit Table. This table would have two records:

Desc NumMonths
-------------- ----------------
IFSPOrig 12
IFSPRedo 24

....and then you'd add table to your forms/reports, and address the NumMonths
field in your DateAdd functions. This would give you the flexibility of
creating more audits, and changing the NumMonths values as needed.

re: Date Calculations

On 29 Jan 2004 12:55:03 -0800, mark_lees@byu.edu (Mark Lees) wrote:

[snip][color=blue]
>I have come up with the following possible expressions:
>IFSPRedo Expression = me!IFSPRedo.value=dateadd("m",12,[IFSPOrignal])
>2yrTransition Expression =
>me!2yrTransistion.value=dateadd("m",24,[DOB])
>
>3 Questions
>Are these 2 Expressions correct?[/color]

You're the only person who can answer that. Test with values on and
around Feb 28 of several years, including at least one leap year, and
see whether you get the answers you want. You should also think about
whether weekends and holidays make a difference.
[color=blue]
>Which field to you put it in?
>
>Where do you write it? In an event?[/color]

Depends on whether it's a constraint on the data. If it is, you need
to put a suitable expression in the validation rule of a table (open a
table in design view, select properties, and so on). But if it's a
constraint, you should think about not storing the value at all.
Instead, calculate it in a query.

If it's not really a constraint, but a helpful suggestion to the data
entry people, think about putting it in the AfterUpdate event of the
control bound to the [DOB] column. And think about whether you want
to overwrite an existing value.

--
Mike Sherrill
Information Management Systems