Summing Fields in a Form. Help!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

On a form, we are trying to sum two fields, which are invoice amounts, and
have that sum display in the next field, on that same form. We have tried
building an expression that sums the two fields using several different
interations, but nothing shows up in the box. A couple of times, we get the
#Name? response.

We queried these two fields and are able to get them to "sum" up to the
correct amount on the "Query" only. We try the same approach on the form and
come up with nothing. In a nut shell, we want the two fields to add up to an
amount that shows up in another field, and all that shows up in the table,
where all this data is stored.

Please, can someone help with our delima? Thanks so much.
 
If you want to add up the two fields in the same record, then try this
In the control source of the field that display the sum, write
=NZ([Invoice1 Field Name In the Table] + nz([Invoice2 Field Name In the
Table],0)

The NZ will replace null with 0

If you want to sum this fields in few records, on the buttom of the form, to
some few records, then in the control source of the field you can write

=Sum(NZ([Invoice1 Field Name In the Table] + nz([Invoice2 Field Name In the
Table],0))
 
Ofer,

That appears to get me half way there. I inputted the second line of code
that you suggested into the control source, directly on the form, in design
view. That does indeed get the two fields to add up to the following field.
However, whenever I enter new numbers, I have to close out of the form and
then re-open it to get the new total. Is there something I missed?

The part that's still missing is getting the data to appear in the table,
where the data is stored. Whenever I try to input that same line of code in
the table, design view, it gives me fits and basically doesn't recognize the
field. How can I get that sum to show up in the table? Do I need to input
code elsewhere, besides on the form?

Thanks for the help.

Ofer said:
If you want to add up the two fields in the same record, then try this
In the control source of the field that display the sum, write
=NZ([Invoice1 Field Name In the Table] + nz([Invoice2 Field Name In the
Table],0)

The NZ will replace null with 0

If you want to sum this fields in few records, on the buttom of the form, to
some few records, then in the control source of the field you can write

=Sum(NZ([Invoice1 Field Name In the Table] + nz([Invoice2 Field Name In the
Table],0))

--
I hope that helped
Good luck


w2mconsulting said:
On a form, we are trying to sum two fields, which are invoice amounts, and
have that sum display in the next field, on that same form. We have tried
building an expression that sums the two fields using several different
interations, but nothing shows up in the box. A couple of times, we get the
#Name? response.

We queried these two fields and are able to get them to "sum" up to the
correct amount on the "Query" only. We try the same approach on the form and
come up with nothing. In a nut shell, we want the two fields to add up to an
amount that shows up in another field, and all that shows up in the table,
where all this data is stored.

Please, can someone help with our delima? Thanks so much.
Click to expand...
Click to expand...
 
The sum field will be updated when the record will be saved, so, on the after
update event of the amount field write the code - Me.Refresh

It is not recomanded to save a calculated field in a table, it will be a
field that you'll have to maintain, and it can cause alot of trouble. All you
need to do, is create a query that will return all the fields in the table
together with a new field, the sum field.

--
I hope that helped
Good luck


w2mconsulting said:
Ofer,

That appears to get me half way there. I inputted the second line of code
that you suggested into the control source, directly on the form, in design
view. That does indeed get the two fields to add up to the following field.
However, whenever I enter new numbers, I have to close out of the form and
then re-open it to get the new total. Is there something I missed?

The part that's still missing is getting the data to appear in the table,
where the data is stored. Whenever I try to input that same line of code in
the table, design view, it gives me fits and basically doesn't recognize the
field. How can I get that sum to show up in the table? Do I need to input
code elsewhere, besides on the form?

Thanks for the help.

Ofer said:
If you want to add up the two fields in the same record, then try this
In the control source of the field that display the sum, write
=NZ([Invoice1 Field Name In the Table] + nz([Invoice2 Field Name In the
Table],0)

The NZ will replace null with 0

If you want to sum this fields in few records, on the buttom of the form, to
some few records, then in the control source of the field you can write

=Sum(NZ([Invoice1 Field Name In the Table] + nz([Invoice2 Field Name In the
Table],0))

--
I hope that helped
Good luck


w2mconsulting said:
On a form, we are trying to sum two fields, which are invoice amounts, and
have that sum display in the next field, on that same form. We have tried
building an expression that sums the two fields using several different
interations, but nothing shows up in the box. A couple of times, we get the
#Name? response.

We queried these two fields and are able to get them to "sum" up to the
correct amount on the "Query" only. We try the same approach on the form and
come up with nothing. In a nut shell, we want the two fields to add up to an
amount that shows up in another field, and all that shows up in the table,
where all this data is stored.

Please, can someone help with our delima? Thanks so much.
Click to expand...
Click to expand...
Click to expand...
 
Well, we have added these two fields up to equal a summed field, all in a
query. Once we view the query, the math works out just like we wrote and we
get the correct results on viewing the query. How can we get that summed
field to show up in our table? By the way, the fields that we are adding up
in the query DO carry over to the table and vise-versa. BUT, the summed
fields DO NOT. We have tried all kinds of expressions and nothing has worked
to bring the summed fields over to the table. What can we do to simply get
these added fields to show up in both query and table?

Also, I tried the Me.Refresh and nothing happened.....I typed in Me.Refresh
and then tried =Me.Refresh in the "after update" field.

Thanks again for the help.

Ofer said:
The sum field will be updated when the record will be saved, so, on the after
update event of the amount field write the code - Me.Refresh

It is not recomanded to save a calculated field in a table, it will be a
field that you'll have to maintain, and it can cause alot of trouble. All you
need to do, is create a query that will return all the fields in the table
together with a new field, the sum field.

--
I hope that helped
Good luck


w2mconsulting said:
Ofer,

That appears to get me half way there. I inputted the second line of code
that you suggested into the control source, directly on the form, in design
view. That does indeed get the two fields to add up to the following field.
However, whenever I enter new numbers, I have to close out of the form and
then re-open it to get the new total. Is there something I missed?

The part that's still missing is getting the data to appear in the table,
where the data is stored. Whenever I try to input that same line of code in
the table, design view, it gives me fits and basically doesn't recognize the
field. How can I get that sum to show up in the table? Do I need to input
code elsewhere, besides on the form?

Thanks for the help.

Ofer said:
If you want to add up the two fields in the same record, then try this
In the control source of the field that display the sum, write
=NZ([Invoice1 Field Name In the Table] + nz([Invoice2 Field Name In the
Table],0)

The NZ will replace null with 0

If you want to sum this fields in few records, on the buttom of the form, to
some few records, then in the control source of the field you can write

=Sum(NZ([Invoice1 Field Name In the Table] + nz([Invoice2 Field Name In the
Table],0))

--
I hope that helped
Good luck


:

On a form, we are trying to sum two fields, which are invoice amounts, and
have that sum display in the next field, on that same form. We have tried
building an expression that sums the two fields using several different
interations, but nothing shows up in the box. A couple of times, we get the
#Name? response.

We queried these two fields and are able to get them to "sum" up to the
correct amount on the "Query" only. We try the same approach on the form and
come up with nothing. In a nut shell, we want the two fields to add up to an
amount that shows up in another field, and all that shows up in the table,
where all this data is stored.

Please, can someone help with our delima? Thanks so much.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top