saving an expression value

[Rpettis31]

I calculate a value but for some reason the value is not saving to the field
in the table. Zero is showing in the record field in the table.

 

[Jeanette Cunningham]

Hi,
access apps usually don't store calculated values in tables.
Storing the value causes problems when the cost of the payment changes and
the calculated value doesn't automatically change.
You end up with inconsistencies in the data for the total payment.
Instead of storing the value, calculate the value each time the form is
opened or each time the report is opened.
This way the value will always be calculated on the current value for cost.

Jeanette Cunningham

 

[Rpettis31]

So if the form was an order form how would you keep track of the values of
each order so that later on you would like to run a report of the total sales
for a period?

 

[Rick Brandt]

So if the form was an order form how would you keep track of the
values of each order so that later on you would like to run a report
of the total sales for a period?

Perform the calculation in a query and use that just as you would have used
a table where the value was stored.

 

[Jeanette Cunningham]

The only snag with this method is the cost price.
If the cost price can change over time, you will need to store the actual
cost price at the time of the sale for this order, so you can use the
correct cost price for the calculation.


Jeanette Cunningham

 

[Ken Sheridan]

You'll find an example in the sample Northwind database which does this.
take a look at the code in the AfterUpdate event procedure of the ProductID
control in the Order Details subform. You'll see that it looks up the
current unit price from the Products table and assigns this to the bound
UnitPrice control in the subform. This is then used to compute the gross
price per item in a computed column in the subform's underlying query. This
is directly analogous to the situation Jeanette referred to.

To produce a total sales amount over a period would be a question of summing
the return value of the same expression used to compute the gross price, e.g.
to return the total sales for a year entered as a parameter value when
prompted:

SELECT [Enter Year:] As Year,
FORMAT(SUM((UnitPrice*Quantity*(1-Discount)/100)*100),"Currency")
AS [Total Sales]
FROM [Order Details] INNER JOIN Orders
ON Orders.OrderID = [Order Details].OrderID
WHERE YEAR(OrderDate) = [Enter Year:];

Ken Sheridan
Stafford, England