Reporting with a Parameterized Cross Tab Query

[bananularstyle]

I have a crosstab query which accepts an input from a combobox and returns data based on the value (<i>month</i>) selected. Because the query can not be generated until a value is selected, the report wizard is unable to display fields for me to add to the report. Does anyone have ideas on how I might workaround or attack this problem.

thanks,

~Leo

 

[kjv1611]

Create a copy of the query to use just for the report.

or

Use VBA and SQL when creating the report, maybe using a button on a form or something. And instead of a parameter query, use the form to build the SQL for the query you are wanting (that way you can use variables, equal to combo boxes or text boxes on your form)

So, you would have a button on your form with code behind it something like this:

Private Sub CreateFormButton_Click()
  Dim strSQL As String
  strSQL = "SELECT Field1, Field2, Field3 " & _
           "FROM Table1 " & _
           "WHERE Field1 = '" & cboComboBox1 & "';"
  'Then create a report with the recordsource set to
  'the new SQL string (strSQL) or either change the source
  'for an existing report to the SQL string.
End Sub

This isn't all you would need, but I think it's an idea that should work.

 

[dhookom]

- Enter a value in the combo box
- Enter values into the Column Headings property of the crosstab

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]

 

[bananularstyle]

I should have mentioned that the column headings are associated with the project that a resource has charged time to (Meaning the headings will change based upon a resource's assignments for that particular month) I've got the query to run correctly but I'm still having issues with the report. It returns the error "The Microsoft Jet database engine does not recognize " as a valid field name or expression." Your help is greatly appreciated.

~Leo

PARAMETERS [Forms]![frmRptMonth].[cboMonth] Text ( 255 );
TRANSFORM Sum(qryFixedPeriodEmpHrs.CategoryPct) AS SumOfCategoryPct
SELECT qryFixedPeriodEmpHrs.Team, qryFixedPeriodEmpHrs.Employee
FROM qryFixedPeriodEmpHrs
WHERE (((qryFixedPeriodEmpHrs.PayMonth)=Forms!frmRptMonth.cboMonth))
GROUP BY qryFixedPeriodEmpHrs.Team, qryFixedPeriodEmpHrs.Employee
ORDER BY qryFixedPeriodEmpHrs.Team, qryFixedPeriodEmpHrs.Category
PIVOT qryFixedPeriodEmpHrs.Category;

-----------------VB Code-----------------------------------
Private Sub cmdAllocOfHrs_Click()
On Error GoTo Err_cmdAllocOfHrs_Click

Dim stDocName As String

If Me.cboMonth > "" Then
Me.cboMonth.SetFocus
intMonthId = Me.cboMonth
strMonth = Me.cboMonth
Else
MsgBox ("You must select a month first")
Me.cboMonth.SetFocus
GoTo Exit_cmdAllocOfHrs_Click
End If

stDocName = "Allocation of Hours by Month"

stLinkCriteria = "[MonthId]=" & Me![cboMonth]

DoCmd.OpenReport stDocName, acPreview

DoCmd.Close acForm, "frmRptMonth", acSaveNo


Exit_cmdAllocOfHrs_Click:
Exit Sub

Err_cmdAllocOfHrs_Click:
MsgBox Err.Description
Resume Exit_cmdAllocOfHrs_Click

End Sub

 

[kjv1611]

I know I've seen that error message before, but I can't think of it's cause off the top of my head. I think, however, that either the program code has a typo in it, or possibly you changed the name of a table or field after writing the code. I could be wrong, but that would be my first guess.

 

[dhookom]

Comments in code:

PARAMETERS [Forms]![frmRptMonth].[cboMonth] Text ( 255 );
TRANSFORM Sum(qryFixedPeriodEmpHrs.CategoryPct) AS SumOfCategoryPct
SELECT qryFixedPeriodEmpHrs.Team, qryFixedPeriodEmpHrs.Employee
FROM qryFixedPeriodEmpHrs
WHERE (((qryFixedPeriodEmpHrs.PayMonth)=Forms!frmRptMonth.cboMonth))
GROUP BY qryFixedPeriodEmpHrs.Team, qryFixedPeriodEmpHrs.Employee
ORDER BY qryFixedPeriodEmpHrs.Team[s], qryFixedPeriodEmpHrs.Category[/s] [red]remove Category from order by[/red]
PIVOT qryFixedPeriodEmpHrs.Category [red]Can't you add the possible column headings here[/red]
;

-----------------VB Code-----------------------------------
Private Sub cmdAllocOfHrs_Click()
On Error GoTo Err_cmdAllocOfHrs_Click

    Dim stDocName As String

    If Me.cboMonth > "" Then
        Me.cboMonth.SetFocus
        intMonthId = Me.cboMonth
        strMonth = Me.cboMonth
    Else
        MsgBox ("You must select a month first")
        Me.cboMonth.SetFocus
        GoTo Exit_cmdAllocOfHrs_Click
    End If

    stDocName = "Allocation of Hours by Month"
    
    stLinkCriteria = "[MonthId]=" & Me![cboMonth][red]this isn't used anywhere[/red]
    
    DoCmd.OpenReport stDocName, acPreview

    DoCmd.Close acForm, "frmRptMonth", acSaveNo][red]don't close the form until after the report is closed[/red]



Exit_cmdAllocOfHrs_Click:
    Exit Sub

Err_cmdAllocOfHrs_Click:
    MsgBox Err.Description
    Resume Exit_cmdAllocOfHrs_Click
    
End Sub

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]