How are calculated FORM dates transferred back into Table?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using the FORM wizard, I created a form from a TABLE. In this TABLE, I had
two date/time fields, one defining the current date and the other defining
the expiration date which was a year later than current date.

In the FORM, I was able to create an expression that would take the
StartDate and add 12 months to create Expiration Date. This worked fine,
except the Expiration Date value would not transferred by into the TABLE.

I want to capture the Expiration Date and provide a monthly report of all
Expiration Dates when they come due.

Appreciate any assistance. Thank you.
 
with rare exceptions, calculated values should not be saved into tables.
since your Start date is stored as hard data, and the Expiration date is the
result of a standard addition of 12 months, you don't need to store the
calculated Expiration date. anytime you need to see the Expiration date in a
form or report, just perform the calculation at that point. to pull upcoming
Expiration dates, create the Expiration date as a calculated field in a
query and set appropriate criteria on that field just as though it were a
hard data field in your table.

hth
 
On Mon, 11 Jul 2005 10:06:03 -0700, Rick in Arizona <Rick in
In the FORM, I was able to create an expression that would take the
StartDate and add 12 months to create Expiration Date. This worked fine,
except the Expiration Date value would not transferred by into the TABLE.
Click to expand...

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.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.
I want to capture the Expiration Date and provide a monthly report of all
Expiration Dates when they come due.
Click to expand...

In this case, just put a calculated field in the query

ExpiriationDate: DateAdd("yyyy", 1, [StartDate])

and put a criterion on this field of

Between Date() And DateAdd("m", 1, Date())

to see all expirations coming up in the next month.

John W. Vinson[MVP]
 
Rick in Arizona said:
Using the FORM wizard, I created a form from a TABLE. In this TABLE, I had
two date/time fields, one defining the current date and the other defining
the expiration date which was a year later than current date.

In the FORM, I was able to create an expression that would take the
StartDate and add 12 months to create Expiration Date. This worked fine,
except the Expiration Date value would not transferred by into the TABLE.

I want to capture the Expiration Date and provide a monthly report of all
Expiration Dates when they come due.

Appreciate any assistance. Thank you.
Click to expand...
 
Back
Top