VBA loop fields of crosstab to create query

[thmsjlmnt3953]

Hi,

the below code works fine if i run a crosstab on a whole table however if i pass criteria to the crosstab e.g between [forms]![form]![txtstartdate] and [forms]![form]![enddate] it fails and returns null for every field? even though if set docmd.runquery "qryReductionByPhysician_Crosstab" this opens with the relevant data?

Set db = CurrentDb
Set qdf = db.QueryDefs("qryReductionByPhysician_Crosstab")
indexx = 0
    For Each fld In qdf.Fields
        If fld.Type >= 1 And fld.Type <= 11 Then
            FieldList = FieldList & "[" & fld.Name & "] as Field" & indexx & ", "
            ReportLabel(indexx) = fld.Name
        End If
        MsgBox indexx
        indexx = indexx + 1
    Next fld
    For i = indexx To 31
            FieldList = FieldList & "null as Field" & i & ","
     Next i
     FieldList = Left(FieldList, Len(FieldList) - 1)
     
 strSQL = "Select " & FieldList & " From qryReductionByPhysician_Crosstab"
 
    db.QueryDefs.Delete "qryCrossTabReport"
    Set qdf = db.CreateQueryDef("qryCrossTabReport", strSQL)
 
 MsgBox strSQL

 

[MarkK]

Where does this occur?

pass criteria to the crosstab

[forms]![form] does not appear in the code. Where and when and how is this criteria passed?

 

[thmsjlmnt3953]

Hi

Its in the crosstab query itself which runs when a cmdbutton is clicked..

PARAMETERS [Forms]![Switchboard]![txtdate] DateTime;
TRANSFORM Count(tblReductionByPhysician.PatientID) AS NumberofPatients
SELECT tblReductionByPhysician.ReductionGroup AS Reduction, Avg(tblReductionByPhysician.PatientID) AS Total
FROM tblReductionByPhysician
WHERE (((tblReductionByPhysician.Physician)=[Forms]![Switchboard]![txtdate]))
GROUP BY tblReductionByPhysician.ReductionGroup
PIVOT tblReductionByPhysician.Physician;

This is the entire code that runs on report_open

Option Compare Database
Option Explicit
Dim ReportLabel(7) As String

Private Sub Report_Open(Cancel As Integer)
'DoCmd.Maximize
Dim i As Integer
    For i = 0 To 7
        ReportLabel(i) = ""
    Next i
    Call CreateReportQuery
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim i As Integer
    For i = 1 To 6
        If FillLabel(i) <> "" Then
            Me("line" & i & "1").Visible = True
            Me("line" & i & "2").Visible = True
            Me("line" & i & "3").Visible = True
        Else
            Me("line" & i & "1").Visible = False
            Me("line" & i & "2").Visible = False
            Me("line" & i & "3").Visible = False
        End If
    Next i
End Sub

Sub CreateReportQuery()
On Error GoTo Err_CreateQuery
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim indexx As Integer
Dim FieldList As String
Dim strSQL As String
Dim i As Integer

Set db = CurrentDb
Set qdf = db.QueryDefs("qryReductionByPhysician_Crosstab")
indexx = 0

    For Each fld In qdf.Fields
        If fld.Type >= 1 And fld.Type <= 11 Then
            FieldList = FieldList & "[" & fld.Name & "] as Field" & indexx & ", "
            ReportLabel(indexx) = fld.Name
        End If
        MsgBox fld.Name
           MsgBox indexx
        indexx = indexx + 1
    Next fld
    For i = indexx To 7
            FieldList = FieldList & "null as Field" & i & ","
     Next i
     FieldList = Left(FieldList, Len(FieldList) - 1)
     
 strSQL = "Select " & FieldList & " From qryReductionByPhysician_Crosstab"
 
    db.QueryDefs.Delete "qryCrossTabReport"
    Set qdf = db.CreateQueryDef("qryCrossTabReport", strSQL)
 
 MsgBox strSQL
 
Exit_CreateQuery:
    Exit Sub
    
Err_CreateQuery:
    If Err.Number = 3265 Then   '*** if the error is the query is missing
        Resume Next             '*** then skip the delete line and resume on the next line
    Else
        MsgBox Err.Description      '*** write out the error and exit the sub
        Resume Exit_CreateQuery
    End If
End Sub

Function FillLabel(LabelNumber As Integer) As String
FillLabel = Nz(ReportLabel(LabelNumber), "")

End Function

 

[MarkK]

Presumably your report's record source is "qryCrossTabReport". Try and change that to "SELECT * FROM qryCrossTabReport"

Also, see if it makes a difference to change this line . . .

Set qdf = db.QueryDefs("qryReductionByPhysician_Crosstab")

. . . to this . . .

Set qdf = CurrentDb.CreateQueryDef("", "SELECT * FROM qryReductionByPhysician_Crosstab")

. . . which creates a new temp QueryDef with the crosstab as a sub-query.

Sometimes Access objects don't work that well with crosstabs directly, but if you subquery those crosstabs, you may get better results. I'm not sure why this is so. Perhaps it forces the crosstab, which has a dynamic set of fields, to complete itself before the Access object tries to figure out what to do with it.

Hope this helps,

 

[MarkK]

Also, if your design-time work with these objects is slow, try dynimcally assigning the recordsource when the form/report opens, so completely delete the recordsource of the report, notice that it goes in and out of design view much more quickly now, and then in report_open, after you've composed all your data sources, do . . .

Me.Recordsource = "SELECT * FROM qryMyCrosstab"

. . . again, sub-querying the crosstab.

 

[thmsjlmnt3953]

Hi

I've just tried your suggestions and im still getting the same problem, this is just an example file im working with at the minute before i implement it into my main database for work, ill upload a copy to let you see exactly what i have.View attachment CrosstabReport2k.mdb

 

[MarkK]

Sorry, but I don't understand what you are trying to do here. It looks like you've done a bunch of experimenting, but for me, looking at it, I don't know what your objective is as apart from your experiments, so I don't know what to "fix."

One observation is that once you've filtered your crosstab, it seems to return data that you could get without a crosstab, and crosstabs are cumbersome. So my question is, apart from the things you are trying here, what is the outcome you need? Maybe we can achieve that in some other, leaner, simpler, way.

 

[thmsjlmnt3953]

Hi,

In my main database i have many departments and each is measured on activity/time and in the current format which they want to keep is displayed as..

columns of dates, rows of names and activity/time (stats) displayed for each employee that day and then to format them in a way to show they have made a target or failed, i hope that helps.

The crosstab itself diplays the data the way i want in the query itself its just trying to get it into a report format dynamically where im stuck.

Thanks