Calculated Field Access 2002

[Cathy C]

I would like to store a calculated field from a form in the table, or
ideally have the field calculated in the table. Presently, an update macro
consists of two queries which 1) calculates the field and stores the data in
a new table, and 2) updates the field in the original table. The calculated
field is in a subform on the form and I would like the field calculated if a
new record is added or an existing payment amount is edited. The process is
not stable and I have started having problems with it again. Any fresh ideas
on how to accomplish this without the macro?

Thanks.
Cathy

 

[Rick Brandt]

I would like to store a calculated field from a form in the table, or
ideally have the field calculated in the table. Presently, an update
macro consists of two queries which 1) calculates the field and
stores the data in a new table, and 2) updates the field in the
original table. The calculated field is in a subform on the form and
I would like the field calculated if a new record is added or an
existing payment amount is edited. The process is not stable and I
have started having problems with it again. Any fresh ideas on how to
accomplish this without the macro?
Thanks.
Cathy

First explain why you feel the need to store a calculated value because this is
a fundamental mistake in a database. One of the primary reasons being exactly
what you are experiencing.

You have a query that does the calculation. Just use that.

 

[Cathy C]

The data is memorialized and exported for comparison to other data for
several different applications and it seems easier to not have to "carry"
this formula around:
=IIf([PPAYFREQ]="B",[PAMT]*26/12,IIf([PPAYFREQ]="M",[PAMT]*12/12,IIf([PPAYFREQ]="S",[PAMT]*24/12,IIf([PPAYFREQ]="W",[PAMT]*52/12,0)))).
If the macro would function correctly and update the field whenever an
addition or change is made, it would be OK. Now I have put a shortcut for
the macro on the desktops so the users who need to export the data can run
it right before they do to make sure they have the correct total for
comparisons. Seems a bit convoluted.

 

[Rick Brandt]

The data is memorialized and exported for comparison to other data for
several different applications and it seems easier to not have to
"carry" this formula around:
=IIf([PPAYFREQ]="B",[PAMT]*26/12,IIf([PPAYFREQ]="M",[PAMT]*12/12,IIf([PPAYFR
EQ]="S",[PAMT]*24/12,IIf([PPAYFREQ]="W",[PAMT]*52/12,0)))).
If the macro would function correctly and update the field whenever an
addition or change is made, it would be OK. Now I have put a shortcut
for the macro on the desktops so the users who need to export the
data can run it right before they do to make sure they have the
correct total for comparisons. Seems a bit convoluted.

Don't let the length of the expression influence you. If you can put that
in a query and it runs in a reasonable amount of time then just export the
query instead of the table. Even if the query were to be a bit slower, slow
and correct beats fast and wrong any day.

 

[Cathy C]

OK. I agree. Thanks for helping me see the light.

The data is memorialized and exported for comparison to other data for
several different applications and it seems easier to not have to
"carry" this formula around:
=IIf([PPAYFREQ]="B",[PAMT]*26/12,IIf([PPAYFREQ]="M",[PAMT]*12/12,IIf([PPAYFR
EQ]="S",[PAMT]*24/12,IIf([PPAYFREQ]="W",[PAMT]*52/12,0)))).
If the macro would function correctly and update the field whenever an
addition or change is made, it would be OK. Now I have put a shortcut
for the macro on the desktops so the users who need to export the
data can run it right before they do to make sure they have the
correct total for comparisons. Seems a bit convoluted.

Don't let the length of the expression influence you. If you can put that
in a query and it runs in a reasonable amount of time then just export the
query instead of the table. Even if the query were to be a bit slower,
slow
and correct beats fast and wrong any day.