Getting the sum in dynamic cross tab report (1 Viewer)

[RogerH72]

Hi all, I would really appreciate if you could help me with this one. I created a dynamic crosstab report with 4 unbound fields in the details section and 4 unbound fields in the header section, which all work perfectly well. However when I want to get the sum in the footer section I get stuck, and I can´t figure out what I´m doing wrong.

The crosstab query contains 17 columns. The last 4 columns contain the values I need to take the sum of.

I have put some code in the open event procedure of the report.

Private Sub Report_Open(Cancel As Integer)

Dim rs As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from Que_ProjectUren_Sel_Dept_Test")

'details section
Me.wk1.ControlSource = rs.Fields(13).Name
Me.WK2.ControlSource = rs.Fields(14).Name
Me.WK3.ControlSource = rs.Fields(15).Name
Me.WK4.ControlSource = rs.Fields(16).Name

´Header section
Me.lblWK1.Caption = rs.Fields(13).Name
Me.lblWK2.Caption = rs.Fields(14).Name
Me.lblWK3.Caption = rs.Fields(15).Name
Me.lblWK4.Caption = rs.Fields(16).Name

´Footer section
‘ I know that the code must be something like this, because when I put the column names from the ‘crosstab query all works well

Me.SumWk1.ControlSource = "=sum([me.wk14])"
Me.SumWk1.ControlSource = "=sum([me.WK15])"
Me.SumWk1.ControlSource = "=sum([me.WK16])"
Me.SumWk1.ControlSource = "=sum([me.WK17])"
rs.Close

End Sub

 

[JHB]

You are setting the ControlSource for the same control 4 times:
Me.SumWk1.ControlSource
Then you are referring to different control as you are setting the ControlSource for:
Me.wk1.ControlSource = rs.Fields(13).Name
...
..
Me.SumWk1.ControlSource = "=sum([me.wk14])"
...
..
I don't think it is necessary to set the ControlSource for this section in the code, because the name is always the same.

Me.SumWk1.ControlSource = "=sum([me.wk14])"
Me.SumWk1.ControlSource = "=sum([me.WK15])"
Me.SumWk1.ControlSource = "=sum([me.WK16])"
Me.SumWk1.ControlSource = "=sum([me.WK17])"

It should be something like that, (and it should be set in design mode):

Me.SumWk1.ControlSource = "=sum([me.wk1])"
Me.SumWk2.ControlSource = "=sum([me.WK2])"
Me.SumWk3.ControlSource = "=sum([me.WK3])"
Me.SumWk4.ControlSource = "=sum([me.WK4])"

 

[Pat Hartman]

Welcome aboard

You CANNOT use aggregate functions on control names. You can only aggregate bound fields. So if the controlName is WK1 and the sourceControl is fld1, then the ControlSource for the sum in the footer is - Sum(fld1). Sum(WK1) will NOT work.

Try:
Me.SumWk1.ControlSource = "=sum([rs.Fields(13).Name])"

 

[RogerH72]

Thank you guys, for your support. I tried your solutions but, unfortunately I'm can't get it right. I'm still getting errors. Is there any other way to get around this?

Many thankx

Roger

 

[JHB]

What error do you get - (error number and error description)?
Post your database with some sample data, (zip it).

 

[RogerH72]

Hi JHB,

I get the "The Microsoft Access database engine does not recognize <name> as a valid field name or expression"error (3070).

Thankx,

Roger

 

[JHB]

Try it now, the database is attached:

 

[RogerH72]

Thank you very much JHB, it works perfectly!!!

Great job!!

 

[JHB]

You're welcome, luck with your project. By the way, did you read my signature?

 

[RogerH72]

Thanks again. You're help was great!

 

[fraserie]

Hi guys...

I have a related question... taking the above database as an example...

If need to add two specific fields together, such as Wk14 and W16 at the detail level. How can this be accomodated?

I have learned that you cannot simply use the SUM operator by simply saying =[Wk1]+[Wk2],... but that is what i'm trying to accomplish.

Please help!!

THansk

 

[JHB]

..
If need to add two specific fields together, such as Wk14 and W16 at the detail level. How can this be accomodated?

Create a control and set the control source:

=Nz([WK1];0)+Nz([WK2];0)

..
I have learned that you cannot simply use the SUM operator by simply saying =[Wk1]+[Wk2],... but that is what i'm trying to accomplish.

For summing, create a control and set the control source:

=Nz([SumWK1];0)+Nz([SumWK2];0)