Field Calculation (1 Viewer)

Hi All
I have two fields say predetermined cost (pcost) and actual cost (acost) now i want a new field total cost (tcost) which includes only whichever is more for pcost and acost, provided manufacturing unit cost is say $8

where does manufacturing cost come into it? Suggest you write out your calculation in words

fields are in tables and the calculated field has limited functionality and is generally avoided by most developers because of this. They also cannot be indexed so if you want to search or filter on tcost, it will be slow compared with doing the calculation in a query.

manufacturing cost is not calculated field you can consider it, for example as price which has fix values

In a Query you can use a calculated field

manufacturing cost is not calculated field you can consider it, for example as price which has fix values

Click to expand...

so where is it stored? in the same table? and why $8? is this a fixed amount or can it change? If so, is that change reflected in some way in the same record?

You have to be specific - computers cannot interpret 'say $8', 'about' etc

CJ_London
it will be stored in same table and its values are not permanent

I would like to clarify something. It is a general (but not an absolute) rule that you do not "store" calculations in a table - though you can certainly store their results. Depending on what you intend to do, you might wish to consider using a SELECT query with some computed fields (such as the IIF noted above) to drive forms or reports. This is because you don't need a table to drive forms or reports. You need recordsets, which you can get from a table OR a query. You can also use calculations in INSERT INTO (a.k.a. "append") and UPDATE queries to accomplish the step of storing your result.

Which path you choose depends on the business rule you are trying to implement. So, to help us better advise you, can you tell us about the business rule both for computing and for retaining this value? How often does it change? How long will it persist? Do you need to actually know the price-change dates long-term for possible research? We need to know things like that to give you the advice you need.

If it is possible for either or both of the cost fields to be null, you ALWAYS have to take that into consideration when writing expressions.

tCost : IIf(Nz([PCost],0) >= Nz([ACost],0), [PCost], [ACost])