Help with some form calculations

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

erick-flores - 07 Aug 2006 21:13 GMT

Hello all

I am having problem doing the fol calculations:

txt1 txt2 txt3 txt4 txt5

I want to be able to SUM all those fields. How do I do that?
Can I do something like Excel =SUM (A2, F8:K8)
where A2 is a field then F8 + G8 + H8...K8 kinda like summarize the SUM
with the " : "

Also, when I want to do a calcualtion, in a form, for a field that is
on a table, how do I wirte that function? e.g. Have a field call Total
Cars (this field is on a table) and I want to SUM other fields and show
the answer in the Total Cars field. Something like:
=Total Cars (SUM [txt1]) thats the idea but I know the function is not
well writen

Thank you in advance

Reply to this Message

Jeff L - 07 Aug 2006 21:36 GMT

I am having problem doing the fol calculations:
txt1 txt2 txt3 txt4 txt5

I want to be able to SUM all those fields. How do I do that?
Can I do something like Excel =SUM (A2, F8:K8)
where A2 is a field then F8 + G8 + H8...K8 kinda like summarize the SUM
with the " : "

Answer: txt1+ txt2 + txt3 + txt4 + txt5. If you are calculating that
value in a textbox on a form, you need an = in front of it.

Also, when I want to do a calcualtion, in a form, for a field that is
on a table, how do I wirte that function? e.g. Have a field call Total
Cars (this field is on a table) and I want to SUM other fields and show
the answer in the Total Cars field. Something like:
=Total Cars (SUM [txt1]) thats the idea but I know the function is not
well writen

Answer: First off, it is not really a good idea to save the results of
a calculation in a field in a database. But to answer your question...
If the field you wish to sum is in your Record Source for your form,
the syntax would be =Sum(YourFieldName).
If the field you wish to sum is not in your Record Source,
=DSum("FieldToSum", "TableName", [Criteria if any])

Hope that helps!

> Hello all
>
[quoted text clipped - 15 lines]
>
> Thank you in advance

Reply to this Message

erick-flores - 07 Aug 2006 21:58 GMT

> Answer: txt1+ txt2 + txt3 + txt4 + txt5. If you are calculating that
> value in a textbox on a form, you need an = in front of it.

Thank you, but what about IF one of the fields does not have a value?
because it works when all the controls have data but if of them does
not have data then it wont display the sum. Any ideas?

> Answer: First off, it is not really a good idea to save the results of
> a calculation in a field in a database.

I though about this, but the thing is I need to figured out a way of
showing this final SUMs in a report. Is there a better way of doing
this?

Reply to this Message

Jeff L - 08 Aug 2006 14:46 GMT

Thank you, but what about IF one of the fields does not have a value?
because it works when all the controls have data but if of them does
not have data then it wont display the sum. Any ideas?

Answer: Nz(txt1,0) + Nz(txt2,0) + Nz(txt3,0) + Nz(txt4,0) + Nz(txt5,0)

I though about this, but the thing is I need to figured out a way of
showing this final SUMs in a report. Is there a better way of doing
this?
Answer: You can perform the calculation right on the report itself,
just like on a form. Just use the calculations that I gave you in my
previous post.

> > Answer: txt1+ txt2 + txt3 + txt4 + txt5. If you are calculating that
> > value in a textbox on a form, you need an = in front of it.
[quoted text clipped - 7 lines]
> showing this final SUMs in a report. Is there a better way of doing
> this?

Reply to this Message

erick-flores - 08 Aug 2006 16:03 GMT

Answer: First off, it is not really a good idea to save the results of

a calculation in a field in a database. But to answer your question...

If the field you wish to sum is in your Record Source for your form,
the syntax would be =Sum(YourFieldName).
If the field you wish to sum is not in your Record Source,
=DSum("FieldToSum", "TableName", [Criteria if any])

I tried doing this one but it wont work, this is what i have:

Field on a table name: Reimb_total
Table Name: Main Table3
Controls Name on Form (dont exist on table): TE1, TE2, TE3, TE4, TE5
Control Source (behind the Reimb_total control in the form):
=DSum("TE1", "Main Table3"), i tried this just for one field and it
didnt work, maybe because I am not adding/sum anything but whats the
right way to write this functions with multiple controls (TE1, TE2,
TE3...). Also The "criteria if any" if there is any criteria I will
just leave close the paranthesis after my table name?

Any ideas? Thank you, by the way the other calculations are working
fine...thnks

Reply to this Message

erick-flores - 08 Aug 2006 16:32 GMT

Quick question, Controls that have a function behind it, e.g.
=Nz([lod1],0)+Nz([lod2],0), will always show (when the form loads) a
value? like I have the properties for that control = Currency, 2
decimals....So when I load the form it will show $0.00, is there way to
NOT show that $0.00 ? In other words I dont want to see anything in the
control if there is any calculation for that control

Reply to this Message

Jeff L - 08 Aug 2006 19:20 GMT

Just take out the ,0 from each one.

> Quick question, Controls that have a function behind it, e.g.
> =Nz([lod1],0)+Nz([lod2],0), will always show (when the form loads) a
> value? like I have the properties for that control = Currency, 2
> decimals....So when I load the form it will show $0.00, is there way to
> NOT show that $0.00 ? In other words I dont want to see anything in the
> control if there is any calculation for that control

Reply to this Message

Jeff L - 08 Aug 2006 19:19 GMT

In order to use DSum the field you are summing must be a field in your
table. If you wish to Sum a field on your form, like in the form
footer, you can put =Sum(TE1). I believe that is what you want fo
Reimb_Total. I don't think you worded the question about the criteria
the way you wanted. If you don't have any criteria, then you would put
the close parenthesis after the table name. Is that what you were
asking?

> Answer: First off, it is not really a good idea to save the results of
>
[quoted text clipped - 19 lines]
> Any ideas? Thank you, by the way the other calculations are working
> fine...thnks

Reply to this Message