Adding controls fields in details

[Stoss]

Hello,

I have a crosstab query that I populate a report with. In the details section, I have 12 textboxes (which are months). I want to sum up all those boxes for each detail (i.e. each row). I was thinking that having an additional text box with (the following code in that textbox control source) would work but it doesn't seem to be.

=Sum([1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12])

(each [x] is the name of the textbox in the details section)

Is this possible to do this and if so, what am I doing wrong?

-Stoss

 

[vbaInet]

For row wise calculations you do it without Sum(), just add them up as you would in simple addition. So:

=[1] + [2] + [3] + ...

If one of the datatypes is not a Number, then you need to use Val() to wrap that field. So:

=Val([1]) + Val([2]) + Val([3]) + ...

Instead of performing this calculation in the control source of a textbox, do it in the record source. That way you will be able to perform a Sum() in the footer section of the report if you ever needed it.

 

[Stoss]

Thank you so much!!!!

I had to put in Nz() function for each because some of the values are Null but it works perfectly now.

*Bows to vbaInet*

When you said do the calculation in the record source, did you mean in the query itself? If so, this is where I was having problems due to the fact that it is a crosstab query and trying to do that go too confusing

-Stoss

 

[vbaInet]

You're welcome!

When you said do the calculation in the record source, did you mean in the query itself? If so, this is where I was having problems due to the fact that it is a crosstab query and trying to do that go too confusing

-Stoss

A Row Heading of Count should do it. Think of it as when you perform a Count in a Totals Query.

I would imagine the column headings are static so you can also perform this calculation by importing all the fields from the crosstab query into a normal Select query and perform the count in there.

 

[Stoss]

If I get time I will look into that but for now you helped me get it up and running, THANKS AGAIN!

-Stoss

 

[vbaInet]

No probs!