What is wrong with my Sum()??

[SimonPGreen]

Hi all,

Blatantly ripped off Getz, Litwin & Gilberts code to create a dynamic report base on a crosstab as follows:

Private Sub Report_Open(Cancel As Integer)
    Dim intColCount As Integer
    Dim intControlCount As Integer
    Dim i As Integer
    Dim strName As String
    On Error Resume Next
    
    Dim rst As adodb.Recordset
    Set rst = New adodb.Recordset
    rst.Open _
        Source:=Me.RecordSource, _
        ActiveConnection:=CurrentProject.Connection, _
        options:=adCmdTable
        
        intColCount = rst.Fields.Count
        intControlCount = Me.Detail.Controls.Count
        If intControlCount < intColCount Then
            intColCount = intControlCount
        End If
        
        For i = 1 To intColCount
            strName = rst.Fields(i - 1).Name
            Me.Controls("lblHeader" & i).Caption = strName
            Me.Controls("txtData" & i).ControlSource = strName
            'Me.Controls("txtSum" & i).ControlSource = "sum([" & strName & "])"
        Next i
        
        For i = intColCount + 1 To intControlCount
            Me.Controls("txtData" & i).Visible = False
            Me.Controls("lblHeader" & i).Visible = False
            Me.Controls("txtSum" & i).Visible = False
        Next i
    
        rst.Close
        
End Sub

Problem I have is that I cannot get the totals in the footer to work with the line

Me.Controls("txtSum" & i).ControlSource = "sum([" & strName & "])"

The field name is a date in the format 04/03/2007 etc however Jet complains that there is an extra ) in query expression '[sum([04/03/2007])]' for example.

Any ideas?

Simon

 

[lespaul]

why would you be adding dates together? Maybe:


Me.Controls("txtSum" & i).ControlSource = "sum([Int(" & Date(strName) & ")])"

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[dhookom]

As much as I respect Getz and Litwin (we all happen to be in Redmond/Seattle at the moment) their solution is not very flexible. I prefer the solution at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Check the Crosstab.mdb in the sample download.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[SimonPGreen]

Thanks for the heads up guys - I will check out the alternative solution today.

I'm new to this and what I am actually trying to do is create a sum at the report footer of all the values falling under the same date?

Regards,

Simon

 

[dhookom]

If you column heading from the crosstab is a date then read the FAQ in this forum faq703-5466.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]