Prev: Share my database
Next: Prevent Full String Selection after Change Event on Control
From: Summing multiple fields on a form Summing multiple fields on a on 22 Mar 2010 18:13
I have created a form but have 4 fields on that form that i would like to sum
and store the total in another field created on the structure. What is the
VB code to do that?
From: Marshall Barton on 22 Mar 2010 18:43
Summing multiple fields on a form <Summing multiple fields
on a form(a)discussions.microsoft.com> wrote:

>I have created a form but have 4 fields on that form that i would like to sum
>and store the total in another field created on the structure. What is the
>VB code to do that?


You should not need any code. Just add a text box in the
form's header or footer sectio and use expressions like:
=Sum(somefield)

If you come back and say that that doesn't store it in
another field, I'll respond that calculated values should
not be stored. Instead they should be recalculated whenever
you need to display the total.

--
Marsh
MVP [MS Access]
From: John W. Vinson on 22 Mar 2010 20:10
On Mon, 22 Mar 2010 15:13:01 -0700, Summing multiple fields on a form <Summing
multiple fields on a form(a)discussions.microsoft.com> wrote:

>I have created a form but have 4 fields on that form that i would like to sum
>and store the total in another field created on the structure. What is the
>VB code to do that?

You've made two mistakes already: building your table wide, not flat, and
trying to store derived data.

You can *display* the sum of four fields by setting a textbox's control source
to

=NZ([field1]) + NZ([field2]) + NZ([field3]) + NZ([field4])

As for storing this value, 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.

--

John W. Vinson [MVP]
From: Summing multiple fields on a form on 23 Mar 2010 04:49
Thank you..It worked just as you said...THX

"John W. Vinson" wrote:

> On Mon, 22 Mar 2010 15:13:01 -0700, Summing multiple fields on a form <Summing
> multiple fields on a form(a)discussions.microsoft.com> wrote:
>
> >I have created a form but have 4 fields on that form that i would like to sum
> >and store the total in another field created on the structure. What is the
> >VB code to do that?
>
> You've made two mistakes already: building your table wide, not flat, and
> trying to store derived data.
>
> You can *display* the sum of four fields by setting a textbox's control source
> to
>
> =NZ([field1]) + NZ([field2]) + NZ([field3]) + NZ([field4])
>
> As for storing this value, 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.
>
> --
>
> John W. Vinson [MVP]
> .
>
 | 
Pages: 1
Prev: Share my database
Next: Prevent Full String Selection after Change Event on Control