Stupid SUM question (1 Viewer)

Kodama76 · Feb 10, 2003

Normally if I want one field totaled I put in a text box with =SUM([product1]) This works great but......

What do you do when you want to total more than one? I tried a text box with =SUM([product1]+[product2]) or =[product1]+[product2] but nothing seems to work.

**a second less imortant question: if the text box for the total is called text9 for example how would I make another text box in the report footer that gives a sum of all the text9's?? If I type =sum([text9]) it prompts me when I open the report and doesn't work at all.**

Jordan76 · Feb 10, 2003

What do you do when you want to total more than one? I tried a text box with =SUM([product1]+[product2]) or =[product1]+[product2] but nothing seems to work.

=SUM([product1]+[product2]) should work, are you putting it in the Report Footer section? Are both the field set to the correct Format? Both Currency so you can do additions? or Number? I dont think you can do it if its both Text. When you use Sum, you need to use the Field Name and not the Text name. So to answer this question:

**a second less imortant question: if the text box for the total is called text9 for example how would I make another text box in the report footer that gives a sum of all the text9's?? If I type =sum([text9]) it prompts me when I open the report and doesn't work at all.**

The answer is, use =sum([Field name (control sourse of Text9)] you cant use the text box name.

Another way you could do both of these is, use a calculated field in a query. and then sum the calculated field on your report.

Hope this helped.

Jordan

Kodama76 · Feb 10, 2003

Thanks...I tried that and I figured out what is causing the problem. The sum only works when both fields have some value.....

I should have mentioned this before but the report is based on a crosstab querey (I have set different products to be the column headings in the querey). Now if I do the sum it works great for one field. It works great as well when I do it for two as long as both fields are not empty.

Any ideas on how to do this? Since this is based on a crosstab querey is it even possible then??

Jordan76 · Feb 10, 2003

It works great as well when I do it for two as long as both fields are not empty.

I had a similiar problem and solved it by putting default values into each box using the properties <default value> box. 0 if numbers or Product for example if its text.

Kodama76 · Feb 10, 2003

Thanks but I can't seem to find the default value box for the textboxes......I went to the properties of the textbox and looked but nothing about default value. Seems odd that I could miss that.

Which tab is it suppossed to be on?

Rich · Feb 10, 2003

=Nz(Sum([Debits]))

Kodama76 · Feb 10, 2003

That did it!! WooHooo!! Thanks a bunch...I was about to give up.

=(Nz(sum([field1])+Nz(sum([field2]))) works the way you want even if one of the two is Null

BernieAnne · Feb 11, 2003

2nd Stupid SUM question

Hi Everyone - I am new to Access and am in the process of putting together my first report. I am also having problems with getting the SUM of two fields. I am making the report from a Query. The two fields I am trying to add together, one comes originally from the table and the other is a field I added in my query. I cannot get these two to add together. I have added a new text box in the footer, then I go to "Properties", click on Control Source and then type in =SUM([Litres]+[Cdn Litres]). Does anyone have any ideas as to what I am doing incorrectly. I have been trying to figure this out for quite some time now and am starting to go insane (lol).

Thanks for any help you can offer.

Bernie

Pat Hartman · Feb 12, 2003

Kodama76,
You need to put the Nz() function around the detail field, NOT the sum:

=sum(Nz([field1])+Nz([field2]))

BernieAnne,
=SUM(Nz([Litres])+Nz([Cdn Litres]))
Make sure that you didn't do something to turn [Cdn Litres] into text. Format() for example. You can turn it back by using the CDbl() function.

=SUM(Nz([Litres])+CDbl(Nz([Cdn Litres])))

BernieAnne · Feb 12, 2003

Pat Hartman

Thank you! Thank you! Thank you! Pat you are a life saver. I never in a million years would have come up with that. As I mentioned in my earlier post I am very new to Access and this is totally different from the DOS database I have used for the past nine years. It took one weekend to learn that programme inside out. This one is going to take me years.

Thanks again and I'm sure I will have more questions for you in the future.

Take care.

Stupid SUM question (1 Viewer)

Kodama76

Registered User.

Jordan76

Registered User.

Kodama76

Registered User.

Jordan76

Registered User.

Kodama76

Registered User.

Rich

Guest

Kodama76

Registered User.

BernieAnne

Registered User.

Pat Hartman

Super Moderator

BernieAnne

Registered User.

Similar threads

Users who are viewing this thread