Storing calculated values in a field

[Guest]

I want to store a calculated value from a text box into a field, this is the
expression that I am using in the text box.

=Val(Nz([incVAIncome],0))*[cmbMonthlyAnnual1]

How do I store the result into a field for later use in a report?

 

[Allen Browne]

See:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

The article describes how - and more importantly, when - to store a
calculated field.

 

[John Vinson]

I want to store a calculated value from a text box into a field, this is the
expression that I am using in the text box.

=Val(Nz([incVAIncome],0))*[cmbMonthlyAnnual1]

How do I store the result into a field for later use in a report?

Don't.

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.

John W. Vinson[MVP]

 

[Guest]

You shouldn't normally store calculated values in tables. However, you can
add code in the form hosting your combo box like:
Me.txtNoNameGiven = Val(Nz([incVAIncome],0))*[cmbMonthlyAnnual1]
Call this code in the after update of the incVAIncome text box and the
cmbMonthlyAnnual1 combo box.

 

[Guest]

ok, I'm not quite getting it. what i want to do is display the calculated
value from:
= Val(Nz([incVAIncome],0))*[cmbMonthlyAnnual1]
into a field on a report. I get why I shouldn't store this value in a field.
I keep seeing that I should perform this calculation in a query. I tried
this but it prompts me for input (enter value for [cmbMonthlyAnnual1]). Is
there a way to get it to run the calculation and output the value to my
report with out being prompted for input?

You shouldn't normally store calculated values in tables. However, you can
add code in the form hosting your combo box like:
Me.txtNoNameGiven = Val(Nz([incVAIncome],0))*[cmbMonthlyAnnual1]
Call this code in the after update of the incVAIncome text box and the
cmbMonthlyAnnual1 combo box.

--
Duane Hookom
Microsoft Access MVP

I want to store a calculated value from a text box into a field, this is the
expression that I am using in the text box.

=Val(Nz([incVAIncome],0))*[cmbMonthlyAnnual1]

How do I store the result into a field for later use in a report?

 

[Guest]

Check Allen Browne's reply. He has a link to code similar to what I suggested.

--
Duane Hookom
Microsoft Access MVP

ok, I'm not quite getting it. what i want to do is display the calculated
value from:
= Val(Nz([incVAIncome],0))*[cmbMonthlyAnnual1]
into a field on a report. I get why I shouldn't store this value in a field.
I keep seeing that I should perform this calculation in a query. I tried
this but it prompts me for input (enter value for [cmbMonthlyAnnual1]). Is
there a way to get it to run the calculation and output the value to my
report with out being prompted for input?

You shouldn't normally store calculated values in tables. However, you can
add code in the form hosting your combo box like:
Me.txtNoNameGiven = Val(Nz([incVAIncome],0))*[cmbMonthlyAnnual1]
Call this code in the after update of the incVAIncome text box and the
cmbMonthlyAnnual1 combo box.

--
Duane Hookom
Microsoft Access MVP

I want to store a calculated value from a text box into a field, this is the
expression that I am using in the text box.

=Val(Nz([incVAIncome],0))*[cmbMonthlyAnnual1]

How do I store the result into a field for later use in a report?

 

[Tom Lake]

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.

John W. Vinson[MVP]

Not always a good idea. If you have to keep track of old rates
you have to store the rate in the table or keep a table of all your
rates with date ranges. Example: The fees charged by home health
aides goes up two to three times a year. We have to know how many
hours and at what rate each patient visit was billed. If we calculate on
the fly, every time the rate table changes, the older visits would be
wrong. The alternative would be to store all historical rates with a
range of dates:
Rate Start End
$75.00 1/3/2006 5/18/2006
$78.00 5/19/2006 8/30/2006
$80.00 8/31/2006 10/31/2006

It's much simpler to grab the current rate and store it in the table.

Tom Lake

 

[missinglinq via AccessMonster.com]

I'm going to agree with Tom Lake on this one; despite the prevailing opinion
in the Access universe, there ARE valid reasons, as he stated, for storing
calculations in a table! And Allen's article explains how to do that when
necessary.

 

[Rick Brandt]

Not always a good idea. If you have to keep track of old rates
you have to store the rate in the table or keep a table of all your
rates with date ranges. Example: The fees charged by home health aides goes
up two to three times a year. We have to know how many hours and at what rate
each patient visit was billed. If we calculate on the fly, every time the
rate table changes, the older visits would be wrong. The alternative would be
to store all historical rates with a range of dates: Rate Start
End
$75.00 1/3/2006 5/18/2006
$78.00 5/19/2006 8/30/2006
$80.00 8/31/2006 10/31/2006

It's much simpler to grab the current rate and store it in the table.

What you are describing is not "storing the result of a calculation". What you
are describing is the copying of related data rather than retrieving it as
needed. You do this because the related data will change and you need a
snapshot in time of the related data. That is a completely correct thing to do
and a completely different thing altogether than storing a calculated value.

You are storing the rate and the hours. Those are the operands that allow you
to later make the calculation for their total charges on-the-fly. If you were
calculating the total and storing that result then THAT would be storing a
calculated value and it would be incorrect.

 

[Rick Brandt]

I'm going to agree with Tom Lake on this one; despite the prevailing opinion
in the Access universe, there ARE valid reasons, as he stated, for storing
calculations in a table! And Allen's article explains how to do that when
necessary.

As I said in my response to Tom. What he described is not "storing calculations
in a table". That doesn't mean that the very rare case doesn't exist where
storing calculations is a reasonable thing to do. It's just not what Tom
described in his post.

One must separate what is "best practice" in database design methods versus what
the technology can cope with. It is never a best practice (theory-wise) to
store a calculated result. However; not doing so (on rare occasions) would make
an application slow enough as to render it useless for the required task. On
those occasions one can store BOTH the result of the calculation as well as all
of the data needed to make the calculation. Then at least one can occasionally
verify the saved calculation by re-running the expression used to create it.

 

[Guest]

I think that what you suggest is exactly what I need to do. I'm entering
client financial assesment data and need to print off a report for each
client right after the data is entered. I've set up the report but can't get
the calculated values displayed in the report textbox like I need them to. I
don't really need the values stored, I just need to be able to print the
result of the calculation on the report. I have tried putting the expression
in a query but since the expression involves a selection from a combo box it
promts me for input. This doesn't work for what I want to do. Is there a
simple way of copying the calculated value into my report? How would I go
about doing it? I'm somewhat new to this so I would appreciate any help you
can give me.

 

[Guest]

You should be able to do this calculation in a query; or the values should be
in the query to be able to do the calc in a control on the form.

Please post the SQL of your query. If you keep getting a prompt, it means
the field [cmbMonthlyAnnual1] (value) is not available to do the calc.

---
It looks like [cmbMonthlyAnnual1] is a combo box. In the query you could try:

TheTotal: Nz([incVAIncome],0)*Nz(Forms!MyForm.[cmbMonthlyAnnual1],0)


replacing "MyForm" with the name of the form [cmbMonthlyAnnual1] is on.

---
Another (better?) way if there is no relationship between the two fields,
would be to write a custom function to do the calculation. In the query you
would have a column like:

Expr1: GetIncomeCalc([EmpID], Year(Date())


And in a standard module, you would have a function something like:


Public Function GetIncomeCalc(pEmpID as Long, pYear as Integer) as Currency
Dim something
Dim SomethingElse

' Get the data from [incVAIncome] and [cmbMonthlyAnnual1]
' using recordsets or lookups

'return the amount
GetIncomeCalc = Nz(Income,0))* MonthlyAnnual

End Function


Of course, this is just an example. Your function would use your field &
table names (and be written correctly).

---
As a last resort, you might be able to use DLOOKUP() to get the values and
do the calc in the control in the report.



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

ok, I'm not quite getting it. what i want to do is display the calculated
value from:
= Val(Nz([incVAIncome],0))*[cmbMonthlyAnnual1]
into a field on a report. I get why I shouldn't store this value in a field.
I keep seeing that I should perform this calculation in a query. I tried
this but it prompts me for input (enter value for [cmbMonthlyAnnual1]). Is
there a way to get it to run the calculation and output the value to my
report with out being prompted for input?

You shouldn't normally store calculated values in tables. However, you can
add code in the form hosting your combo box like:
Me.txtNoNameGiven = Val(Nz([incVAIncome],0))*[cmbMonthlyAnnual1]
Call this code in the after update of the incVAIncome text box and the
cmbMonthlyAnnual1 combo box.

--
Duane Hookom
Microsoft Access MVP

I want to store a calculated value from a text box into a field, this is the
expression that I am using in the text box.

=Val(Nz([incVAIncome],0))*[cmbMonthlyAnnual1]

How do I store the result into a field for later use in a report?

 

[John Vinson]

I have tried putting the expression
in a query but since the expression involves a selection from a combo box it
promts me for input. This doesn't work for what I want to do. Is there a
simple way of copying the calculated value into my report? How would I go
about doing it? I'm somewhat new to this so I would appreciate any help you
can give me.

If the Form is open, you can include a criterion or a calculated field
referencing

[Forms]![NameOfTheForm]![NameOfTheCombo]

in your query, or in the Control Source of a textbox on the Report.

If you do decide that it's a good idea to store the derived data (and
I doubt that it is, given the concerns above) then you can use two
controls on the Form: txtCalc and txtStored, say. The control source
of txtCalc would be your expression; the control source of txtStored
would be the table field into which you want to (redundantly and
dangerously <g>) store the result of the calculation.

In the Form's BeforeUpdate event, simply include a line

Me.txtStored = Me.txtCalc

to push the calculated value into the table field.

John W. Vinson[MVP]

 

[Guest]

I put the expression
=[Forms]![frmFinancialAssessment]![cmbMonthlyAnnual1]
into the textbox control souce on my report ant when I view the report I get
"#Error" in the field. I also tried some other people's suggestions about
using a query and I always get prompted for input about the combo box. I'm
guessing this is because the calculations depends on what a user selects from
the combo box. The value calculates fine on the form, I can't figure out why
it won't display correctly on the report. any ideas?

I have tried putting the expression
in a query but since the expression involves a selection from a combo box it
promts me for input. This doesn't work for what I want to do. Is there a
simple way of copying the calculated value into my report? How would I go
about doing it? I'm somewhat new to this so I would appreciate any help you
can give me.

If the Form is open, you can include a criterion or a calculated field
referencing

[Forms]![NameOfTheForm]![NameOfTheCombo]

in your query, or in the Control Source of a textbox on the Report.

If you do decide that it's a good idea to store the derived data (and
I doubt that it is, given the concerns above) then you can use two
controls on the Form: txtCalc and txtStored, say. The control source
of txtCalc would be your expression; the control source of txtStored
would be the table field into which you want to (redundantly and
dangerously <g>) store the result of the calculation.

In the Form's BeforeUpdate event, simply include a line

Me.txtStored = Me.txtCalc

to push the calculated value into the table field.

John W. Vinson[MVP]

 

[Guest]

Robert,

The guys that have been responding to this post (excluduing me) are
extremely proficient programmers. I've been reading the newsgroup for over
two years an these are the guys I look to for answers - whether or not I post
the question.

But as good as they are, even they cannot help you if all you can tell them
is "It doesn't work". Only you can see your forms, code, reports and queries.

It would be very helpful if you would

... post the SQL of the query
... give examples of the data - values you have and results of the calculation
... maybe the SQL of the rowsource of the combo box
... the form name an if the form is open when you run the query


If I ask you tell me what is wrong with my car and all I tell you is "It
doesn't work. I turned the key and nothing happened", you don't know if I
have a diesel or gas engine. Or if the key is in the trunk or the ignition.
(I know - extreme example - but you get the idea).


I apologize if I sound harsh, I don't mean to be. But to get your question
answered, you need to provide more info.

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I put the expression
=[Forms]![frmFinancialAssessment]![cmbMonthlyAnnual1]
into the textbox control souce on my report ant when I view the report I get
"#Error" in the field. I also tried some other people's suggestions about
using a query and I always get prompted for input about the combo box. I'm
guessing this is because the calculations depends on what a user selects from
the combo box. The value calculates fine on the form, I can't figure out why
it won't display correctly on the report. any ideas?

I have tried putting the expression
in a query but since the expression involves a selection from a combo box it
promts me for input. This doesn't work for what I want to do. Is there a
simple way of copying the calculated value into my report? How would I go
about doing it? I'm somewhat new to this so I would appreciate any help you
can give me.

If the Form is open, you can include a criterion or a calculated field
referencing

[Forms]![NameOfTheForm]![NameOfTheCombo]

in your query, or in the Control Source of a textbox on the Report.

If you do decide that it's a good idea to store the derived data (and
I doubt that it is, given the concerns above) then you can use two
controls on the Form: txtCalc and txtStored, say. The control source
of txtCalc would be your expression; the control source of txtStored
would be the table field into which you want to (redundantly and
dangerously <g>) store the result of the calculation.

In the Form's BeforeUpdate event, simply include a line

Me.txtStored = Me.txtCalc

to push the calculated value into the table field.

John W. Vinson[MVP]

 

[John Vinson]

I put the expression
=[Forms]![frmFinancialAssessment]![cmbMonthlyAnnual1]
into the textbox control souce on my report ant when I view the report I get
"#Error" in the field. I also tried some other people's suggestions about
using a query and I always get prompted for input about the combo box. I'm
guessing this is because the calculations depends on what a user selects from
the combo box. The value calculates fine on the form, I can't figure out why
it won't display correctly on the report. any ideas?

Is the Form named frmFinancialAssessment in fact open at the time that
you're opening the Report? One way to ensure that it is would be to
open the report using a command button on that Form.

Another possible problem - is frmFinancialAssessment in fact a Subform
of some other Form? If so, it's not in the [Forms]! collection, and
must be accessed via its mainform.

John W. Vinson[MVP]