Sum in Detail section

[kbreiss]

I've searched several pages regarding sums and I'm still having trouble.....In my detail section I want a sum of each row. I thought it would be a simple textbox w/ its recordsource as =sum([count1]+[count2]+[count3]), but I have worked all afternoon on it w/ out getting it right. This report already comes from three different queries. Is there anyway just to some control sources?

Here's what my report looks like....

number count1 count2 count3 sum
1 5 7 10 ?
2 0 9 8 ?, etc.

I'm trying to get a sum in the detail section.

Any help would be appreciated.

Thanks,
Kacy
________
Dominatorqueen

 

[Pat Hartman]

Sum() sums data from different rows. That doesn't make sense in the detail section since the detail section is produced for each row. Sum() is only logical in a footer.

You may just want to add several columns together. In that case:

=Nz(count1,0) + Nz(count2,0) + ...

I used the Nz() function because if any of the fields you are adding is null, the result will be null and the Nz() will eliminate that problem.

 

[kbreiss]

Pat,
When I tried the code you gave me in the total field I'm getting the columns concatenated together.

Say I have

7 5 3 = (its giving me) 753...........instead of 15.

Any ideas why it's doing this? These are numeric fields.

Thanks,
Kacy
________
DEPAKOTE ATTORNEYS

 

[Pat Hartman]

The field data types must be text rather than number.

 

[kbreiss]

Pat,
I thought the same thing that the fields must be text. I noticed it would allow me to take the field and multiply it though. I tried doing something like this. =count1 *1 + count2*1 + count3 *1 and it summed the fields correctly. Strange, but it worked. Just thought I would pass it on.


Kacy
________
ANGER MANAGEMENT FORUMS

 

[Pat Hartman]

The plus (+) sign serves double duty as both an arithmetic operator and a concatenation operator. Because the fields are text you cannot use them in an arithmetic operation therefore, Access is interpreting the + as a concatenation operator. Access is very flexible though and will attempt to perform operations on fields even though they are the wrong data type as long as the data is valid for the operation being performed. Therefore, multiplying a text field will result in a correct answer if the text field does in fact contain a numeric value.

If you are not going to change the data type to numeric, at least use code that won't fail if your text field contains a text value.

=Val(count1) + Val(count2) + ....

The Val() function returns any leading numeric characters from a text field. Of course if your data is not an integer, this won't work. You'll need to use the cdbl() function instead.