Question Adding two totals together

[fudgematico]

Very sorry this is probably all over the internet but I cant seem to figure it out :S

I am trying to add two columns with multiple records in them together using VBA.

I have one column named "Quantity" and that has 5 records going down
Column1(Row1) = 2
Column1(Row2) = 3
Column1(Row3) = 1
Column1(Row4) = 2
Column1(Row5) = 2

I have another column name "Quantity2" and that has another 5 records going down

Column2(Row1) = 1
Column2(Row2) = 4
Column2(Row3) = 2
Column2(Row4) = 2
Column2(Row5) = 2

I need a code to simply add them all together together using VBA and put the value back in a text box.

So the Value in the text box would be = 21

Thanks for the help in advance

 

[pbaldy]

Try

DSum("Column1 + Column2", "TableName")

 

[fudgematico]

Where would I apply this? to the click event on the text box?

I would need it so when I press a button it will put the value in a text box. Or even better without the button and it just updates on its own?

 

[pbaldy]

You could have it behind one of those events, or simply make it the control source of one:

=DSum(...)

 

[Pat Hartman]

If you are displaying this data on a subform (or mainform), you can put controls in the form footer to sum the data. I would avoid DSum() if possible.

In the controls in the subform use something like the following

SumControl1 as the name of the control
=Sum(Field1) as the ControlSource

SumControl2 as the name of the control
=Sum(Field2) as the ControlSource

TotalControl as the name of the control that sums the two.
=Sum(Nz(Field1) + Nz(Field2))

OR - since the controls are in the same section of the form, you can use

=Control1 + Control2

Notice that when I added the two columns inside the function, I used the Nz() function. This will handle the situation where one or both of the fields is null. Aggregate functions such as Sum(), Avg(), etc. ignore null values so you don't need to worry about them in the first examples. You only need to account for nulls when doing "arithmetic" with fields that might contain nulls. In the final example where I added the two controls, I didn't use Nz() but I would verify that will work. I think it will but verify.

 

[fudgematico]

Thanks for the reply

I think that the code will work but I am having trouble getting the columns to add together. Heres my code:

=Sum([tblTest subform 1].[Form]![Quantity])

I already tried =Sum([Quantity])

But it didnt work

Any suggestions?

 

[bob fitz]

I already tried =Sum([Quantity])

But it didnt work

Where did you try. The textbox needs to be in the Form Header/Footer section as Pat said.

 

[fudgematico]

Yh it is in the footer but I still get the error "#Error" coming up in the text box?

Any help?

 

[fudgematico]

Bump

Please help

 

[fudgematico]

Problem solved!

I had to go into my subform in design view and put the total text box in the footer of that.
Then I just put that subform in a form and referenced to the total text box inside the subform using another text box in the form.

Thanks all for help