Sum function in a form

[eptx]

Hi,

I have a form with 4 different fields.

field 1 = Item Box
field 2= Shipping Box
field 3= Tax Box
field 4= Total Box

I need to add the first 3 fields together to get field4, I've tried several different formulas and nothing seems to work. I don't have a subform and I wonder if I need one or if I can get by without one? This is what I have in the control source for field4.

=Sum([item]+[shipping]+[tax]

The result that I'm getting is a total for the entire table, not for my form?
What if I have a null value in one of the fields?

Please help!!!! Access 2007

 

[MStef]

Try this:
Me.FieldName4 = me.FieldName1 + me.FieldName2 + me.FieldName3

 

[eptx]

This is what i got?

#Name?

 

[missinglinq]

That would work in VBA code but in the Control Source for Field4 you'd simply put

= [FieldName1] + [FieldName2] + [FieldName3]

 

[RainLover]

Try

= NZ([FieldName1],0) + NZ([FieldName2],0) +NZ([FieldName3],0)

This is in case you have a Null Field.

 

[eptx]

Hi there!

I used your formula and received a 0 (zero) as result? Are there any settings in the fields that I should be looking at?

Thanks

 

[Marinus]

Hi there!

I used your formula and received a 0 (zero) as result? Are there any settings in the fields that I should be looking at?

Thanks

When you open the table in Design view, are these fields numeral??

 

[eptx]

I tried this formula and I can't get a result in the last total box?

 

[eptx]

Each field was set up as Data Type: Currency and Format: General Number.

Is there an option that will refresh a field automatically?

 

[Marinus]

Each field was set up as Data Type: Currency and Format: General Number.

Is there an option that will refresh a field automatically?

This should do it for you.. it's in Access 2007 give a shout if you have other version

 

[RainLover]

If you are using a Query as your record source then place this
AAA: NZ([FieldName1],0) + NZ([FieldName2],0) +NZ([FieldName3],0)
in a new column.
Then open the query.

PS

Is this supposed to be currency or a number?

 

[missinglinq]

Actually, since the OP stated that he was doing this in the ControlSource, RainLover's

= NZ([FieldName1],0) + NZ([FieldName2],0) +NZ([FieldName3],0)

should work even if, as he stated, there are Nulls in one or more fields.

It may seem a silly question, but you did replace FieldName1, FieldName2, and FieldName3 with your actual names, didn't you?

 

[eptx]

Thank you very much for your answer. Yes I did change the names.

 

[eptx]

Thank you very much for your help. the attachment is what I needed. I really appreciate your help. IT WORKS!!!

PS. I know.. this is my first Database. This forum is amazing. I will probably have more questions so I'm sure I will see you around.

 

[eptx]

How can I record the result from the total field into my table?

 

[missinglinq]

It is very seldom advisable to store a calculated value! Instead you should simply run the same calculation again, as needed, in reports or other forms.

1. Doing so chews up more storage memory
2. Retrieving data from a table is more labor intensive, processor-wise, than doing a calculation, especially one as simply as this.
3. In some circumstances component data can be changed elsewhere and the stored total won't reflect the change.

Linq ;0)>

 

[eptx]

Very good explanation. I was doing some research and basically found that it was not recommended to store that kind of data, however nobody was able to explain why. Thanks again.

 

[eptx]

Hello there!

My DB is looking pretty good, however I came up with a small issue.

My formula is rounding the number to the highest number, regardless of the number I type in my formula.

I have four fields:
Item
Shipping
Tax
Total

This is the formula I'm using.

=NZ([Item],0)+NZ([Shipping],0)+NZ([Tax],0)

What if any of the fields has no value at all (zero)?

 

[boblarson]

I have four fields:
Item
Shipping
Tax
Total

In the table, those fields aren't set as NUMBER - Field Length of Integer or Long Integer are they? Instead of NUMBER they should be CURRENCY from the datatype drop down.

 

[eptx]

Didn't work.