Recalculation of Existing Fields (A2K)

[AGMCon]

To make a short story long, it was necessary to change the labels on a large number of fields used to arrive at a specific calculation called HardCosts.

Before Changes:

HardCosts: NZ([HardCosts1])+NZ([HardCosts2])

The fields in HardCosts1 & HardCosts2 and in all applicable locations were renamed to a shorter label. When these fields were combined into the new version of HardCosts, the calculations were 'lost'. Any new record created works perfectly and the new calculation in HardCosts is correct.

And the question is,

How can I 'force' the recalculation of the newly relocated fields inside of HardCosts?

Looking forward to any help,

[HansV]

I'm sorry, I don't understand. Could you try to explain again? Are you talking about tables or queries or forms?

[AGMCon]

Hans, sorry for the confusion. I didn't want to belabour you with too many details, but I guess without the details, it obviously doesn't make sense.

The original scenario was that there were a large number of fields in a table that were used in a query to calculate a total called 'HardCosts'. Because there were so many fields, 61 to be exact and because their field names were so long in order to 'facilitate' recognition, they could not all be included in one calculation which was going to be 'HardCosts'. This was due to Access length restrictions. So the 61 fields were split into two calculated fields, 'HardCosts1' and 'HardCosts2'. And this has been working for a very long time.

Now, not one to leave well enough alone, I went back into the system and renamed the fields using shorter field names so that they could in fact be used all in one calculation, ie. HardCosts.

As mentioned earlier, all of the renaming was totally successful and any new record has its HardCosts calculated correctly. The problem that I have is that the original fields which are now all in HardCosts will not recalculate. And the question is, can I force a one-time recalculation or refresh for these fields.

I hope I haven't clouded the issue too much,

[HansV]

An update query is probably what you need to do the one-time calculation, but since I still fail to understand your description, I can't give specific advice.

[AGMCon]

Hans, you certainly are patient. I obviously have to take a course in Clarity of Explanations.
OK, here's a short version of the problem.

Before the problem:

HardCost1: Fld A + Fld B

HardCost2: Fld C + Fld D

HardCost: HardCost1 + HardCost2

After the problem:

HardCost: Fld A + Fld B + Fld C + Fld D

Previous Fld A's thru Fld D's will not give me the new HardCost
New Records will.

How do I make HardCost recalculate, one time only, with Fld A's thru Fld D's

Hope this helps.

[charlotte]

If you are storing this value in the field from a form and you have the HardCosts calculation as a default, you will see exactly the behavior you described. Defaults only apply to new records. To correct the existing HardCosts records, you need to do an update query on the entire table and recalculate the HardCosts value. If HardCosts is NOT a field in the table but only for display on the form, then you need the form use use the sum of those individual fields as the controlsource for HardCosts.

Any BTW, the problems you appear to be describing are a primary reason why we all recommend that you NOT store calculated values in a field.

[AGMCon]

Charlotte,

It's really nice to hear from you again. I guess I thought that if you had CALCULATIONS defined in a QUERY, like HARDCOST, HARDCOST1 and HARDCOST2 that there might be a way to make them recalculate. These calculated fields are made up data inputted into fields residing on a form based on a query based on a table.

None of the above are stored in the table. Surprisingly, even I, along with the rest of the 'we', knew that one.

I guess I'm too clear on exactly how you would do an update query on the entire table. If you could be kind enough to elaborate on that particular approach, I'd appreciate it.

[HansV]

If HardCost is not a field in the table, there is no need for an update query. Update queries are meant to change field values in a table.

If you have a calculated column HardCost: Fld A + Fld B + Fld C + Fld D in a query, HardCost should be calculated for each record, but if one or more of the contributing fields is blank (null), the result will be blank too. If you want to get a value for HardCost even if one or more of the fields is blank, use

HardCost: Nz([FldA], 0) + Nz([FldB], 0) + Nz([FldC], 0) + Nz([FldD], 0)'

If that doesn't do what you want, please give an example of what goes wrong.

[AGMCon]

Hans, it's REALY, REALY nice to hear from you. Now this is the part where I curse, wail and gnash my teeth. Buried deep inside HARDCOST was one lousy field that I did not put the NZ in front of. So you know what my results were. Nada. I thank you very kindly, and also have one small question. When I have fields in a query such as this one I define it as NZ([Cost1]) as a for instance. What is the advantage if I define it as NZ([Cost1],0) ? Just curious.
Anyway, thanks again for coming to my rescue.

[HansV]

Strictly speaking, you don't need the zero here. The syntax for Nz is Nz(value, value_if_null). This function normally returns value, but if value is null, it returns value_if_null. If you omit value_if_null, the most appropriate value is used: a zero length string "" for string values, 0 for numeric values. However, I tend to specify the second argument explicitly so that it is immediately clear what the function will return.

[AGMCon]

You learn something new every day. Thanks again, Hans.