My calculated expression field is not being populated in the table

[Guest]

I have a 'Calendar Year' field on a data entry form that uses an expression
to extract the four digit year from another field called 'Ship Date' in the
same form. That expression is working fine in the form, but the 'Calendar
Year' is not being populated in the table. I must be missing a link or a
setting?

 

[John Vinson]

On Wed, 2 Feb 2005 13:33:05 -0800, Curt S <Curt

I have a 'Calendar Year' field on a data entry form that uses an expression
to extract the four digit year from another field called 'Ship Date' in the
same form. That expression is working fine in the form, but the 'Calendar
Year' is not being populated in the table. I must be missing a link or a
setting?

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. Simply use a
Textbox on any form or report with a Control Source of

=Year([Ship Date])

John W. Vinson[MVP]

 

[Guest]

I can get that but the problem is that I have a prompt to run the report
which is 'Calendar Year'. ie: I want to see all shipments for 2005. Right
now I have the report looking at a calendar year query and the query criteria
points to a 'calendar year' form. So if I could link the shipment
information table with a calendar year table, I could make it work.

On Wed, 2 Feb 2005 13:33:05 -0800, Curt S <Curt

I have a 'Calendar Year' field on a data entry form that uses an expression
to extract the four digit year from another field called 'Ship Date' in the
same form. That expression is working fine in the form, but the 'Calendar
Year' is not being populated in the table. I must be missing a link or a
setting?

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. Simply use a
Textbox on any form or report with a Control Source of

=Year([Ship Date])

John W. Vinson[MVP]

 

[Rick Brandt]

I can get that but the problem is that I have a prompt to run the
report which is 'Calendar Year'. ie: I want to see all shipments for
2005. Right now I have the report looking at a calendar year query
and the query criteria points to a 'calendar year' form. So if I
could link the shipment information table with a calendar year table,
I could make it work.

So link it to a calendar year *query* instead. There is nothing you can do with
a table that you cannot also do with a query.