Further query for Allen Browne

[Guest]

Allen -
Thanks for clarifying my earlier query re queries for calculated fields,
that's fine now.
I have several calc fields that I really do need to store in the table and
cannot quite finish this off:
I have four calculated cost lines and I have now managed to get those to
store individually in the table by using the AfterUpdate event code you
provide in your article.
However I also need to store the total of those four fields and I have tried
all sorts of variations in the code and just cannot get it to save in the
Total field in the table. I tried After Update on the individual cost fields,
and several other routes, but no good.
I am aware of the potential risks of doing this but these are quotation
figures that are set in stone once quoted to the client and I do need to
store them permanently.

Thanks for your help!
CW

 

[Allen Browne]

You haven't convinced me that it is a good design to store the calculated
total. The only "valid" reason for doing that would be if the stored total
should sometimes be different than the sum of the items. For example, if you
wanted to cheat the client and charge extra hoping he did not notice that
the numbers don't add up like this:
Item1 $100
Item2 $120
Total: $1120

If that's what you want to do, the AfterUpate code will do it:
Me.Total = Nz(Me.Item1,0) + Nz(Me.Item2,0)

 

[Guest]

Thanks, Allen - on which field(s) should the AfterUpdate code be applied?
I have tried it on the individual cost fields (the components that go
towards the Total) and also on the Total field itself, but cannot get the
Total to store. It doesn't error, just doesn't save in the table!

I guess the alternative is to use a Query for the Total, and presumably that
would go in the Control Source for Total on the form, and would look like
this:
Total: [Item1] + [Item2].

And when I need to use the Total figure elsewhere i.e. in merged documents
or in reports, will it pull through OK?

Three separate questions here, sorry!!

Many thanks
CW

 

[Allen Browne]

The code would need to go into the AfterUpdate of each text box that the
total depends on. You can write the code in the first one, and then call it
in the others like this:
Call Item1_AfterUpdate

Yes, The calculated query field you suggested is the much better solution.
You can use the query as the source anywhere you can use a table, and the
total can never be wrong.

Don't forget to use Nz() in case any one of the values is null:
Total: Nz([Item1],0) + Nz([Item2],0)
It's also a good idea to wrap the whole thing in CCur() so Access knows to
treat the total as a number:
Total: CCur(Nz([Item1],0) + Nz([Item2],0))

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen - on which field(s) should the AfterUpdate code be applied?
I have tried it on the individual cost fields (the components that go
towards the Total) and also on the Total field itself, but cannot get the
Total to store. It doesn't error, just doesn't save in the table!

I guess the alternative is to use a Query for the Total, and presumably
that
would go in the Control Source for Total on the form, and would look like
this:
Total: [Item1] + [Item2].

And when I need to use the Total figure elsewhere i.e. in merged documents
or in reports, will it pull through OK?

Three separate questions here, sorry!!

Many thanks
CW

You haven't convinced me that it is a good design to store the calculated
total. The only "valid" reason for doing that would be if the stored
total
should sometimes be different than the sum of the items. For example, if
you
wanted to cheat the client and charge extra hoping he did not notice that
the numbers don't add up like this:
Item1 $100
Item2 $120
Total: $1120

If that's what you want to do, the AfterUpate code will do it:
Me.Total = Nz(Me.Item1,0) + Nz(Me.Item2,0)