HELP NEEDED WITH REPORT

[Alan]

Please help

I keep getting blank report

on my print preview button i have this as part of code

Sub PrintReports(ReportView As AcView)

Dim strReportName As String
Dim strReportFilter As String
Dim lOrderCount As Long

' Determine report filtering
If Nz(Me.lstReportFilter) <> "" Then
strReportFilter = "([SalesGroupingField] = """ & Me.lstReportFilter
& """)"
End If

' Determine reporting time frame
Select Case Me.lstSalesPeriod
Case ByYear
strReportName = "Yearly Sales Report"
lOrderCount = DCountWrapper("*", "Sales Analysis", "[Year]=" &
Me.cbYear)

If lOrderCount > 0 Then
TempVars.Add "Group By", Me.lstSalesReports.value
TempVars.Add "Display", DLookupStringWrapper("[Display]", "Sales
Reports", "[Group By]='" & Nz(Me.lstSalesReports) & "'")
TempVars.Add "Year", Me.cbYear.value
TempVars.Add "Quarter", Me.cbQuarter.value
TempVars.Add "Month", Me.cbMonth.value

eh.TryToCloseObject
DoCmd.OpenReport strReportName, ReportView, , strReportFilter,
acWindowNormal
Else
MsgBoxOKOnly NoSalesInPeriod
End If
End Sub



WHICH THEN OPEN THE YEARLY SALES REPORT

Private Sub Report_Open(Cancel As Integer)
On Error GoTo ErrorHandler

Dim strSQL As String

If IsNull(TempVars![Display]) Or IsNull(TempVars![Group By]) Or
IsNull(TempVars![Year]) Then
DoCmd.OpenForm "Actual Report"
Cancel = True
Exit Sub
End If

strSQL = "TRANSFORM CCur(Nz(Sum([Amount]),0)) AS X"
strSQL = strSQL & " SELECT [" & TempVars![Display] & "] as
SalesGroupingField FROM [Sales Analysis] "
strSQL = strSQL & " Where [Year]=" & TempVars![Year]
strSQL = strSQL & " GROUP BY [" & TempVars![Group By] & "], [" &
TempVars![Display] & "]"
strSQL = strSQL & " Pivot [Sales Analysis].[Quarter] In (1,2,3,4)"

Me.RecordSource = strSQL
Me.SalesGroupingField_Label.Caption = TempVars![Display]

Done:
Exit Sub
ErrorHandler:
' Resume statement will be hit when debugging
If eh.LogError("Yearly Sales Report_Open", "strSQL = " & strSQL) Then
Resume
Else
Cancel = True
End If
End Sub

BUT EVERYTIME I PRESS THE PRINT PREVIEW BUTTON THE REPORT IS BLANK
EVEN THOUGHT MY FORM HAS SELECTED A CUSTOMER AND A YEAR,

CAN ANYONE HELP

 

[mcescher]

Please help

I keep getting blank report

on my print preview button i have this as part of code

Sub PrintReports(ReportView As AcView)

    Dim strReportName As String
    Dim strReportFilter As String
    Dim lOrderCount As Long

    ' Determine report filtering
    If Nz(Me.lstReportFilter) <> "" Then
        strReportFilter = "([SalesGroupingField] = """ & Me.lstReportFilter
& """)"
    End If

    ' Determine reporting time frame
    Select Case Me.lstSalesPeriod
    Case ByYear
        strReportName = "Yearly Sales Report"
        lOrderCount = DCountWrapper("*", "Sales Analysis", "[Year]=" &
Me.cbYear)

 If lOrderCount > 0 Then
        TempVars.Add "Group By", Me.lstSalesReports.value
        TempVars.Add "Display", DLookupStringWrapper("[Display]","Sales
Reports", "[Group By]='" & Nz(Me.lstSalesReports) & "'")
        TempVars.Add "Year", Me.cbYear.value
        TempVars.Add "Quarter", Me.cbQuarter.value
        TempVars.Add "Month", Me.cbMonth.value

        eh.TryToCloseObject
        DoCmd.OpenReport strReportName, ReportView, , strReportFilter,
acWindowNormal
    Else
        MsgBoxOKOnly NoSalesInPeriod
    End If
End Sub

WHICH THEN OPEN THE YEARLY SALES REPORT

Private Sub Report_Open(Cancel As Integer)
    On Error GoTo ErrorHandler

    Dim strSQL As String

    If IsNull(TempVars![Display]) Or IsNull(TempVars![Group By]) Or
IsNull(TempVars![Year]) Then
        DoCmd.OpenForm "Actual Report"
        Cancel = True
        Exit Sub
    End If

    strSQL = "TRANSFORM CCur(Nz(Sum([Amount]),0)) AS X"
    strSQL = strSQL & " SELECT [" & TempVars![Display] & "] as
SalesGroupingField FROM [Sales Analysis] "
    strSQL = strSQL & " Where [Year]=" & TempVars![Year]
    strSQL = strSQL & " GROUP BY [" & TempVars![Group By] & "], [" &
TempVars![Display] & "]"
    strSQL = strSQL & " Pivot [Sales Analysis].[Quarter] In (1,2,3,4)"

    Me.RecordSource = strSQL
    Me.SalesGroupingField_Label.Caption = TempVars![Display]

Done:
    Exit Sub
ErrorHandler:
    ' Resume statement will be hit when debugging
    If eh.LogError("Yearly Sales Report_Open", "strSQL = " & strSQL) Then
        Resume
    Else
        Cancel = True
    End If
End Sub

BUT EVERYTIME I PRESS THE PRINT PREVIEW BUTTON THE REPORT IS BLANK
EVEN THOUGHT MY FORM HAS SELECTED A CUSTOMER AND A YEAR,

CAN ANYONE HELP

Right before you get to this line,
Me.RecordSource = strSQL

put in a
Debug.Print strSQL

That will show you what your SQL string looks like. Check that for
errrors. You can also paste that into the SQL view of a new query and
see if it runs or gives you error messages.

Hope this helps,
Chris M.