Change a report to a query

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

i have this code which looks up my sales analysis table,
i specify what customer and month i want in my dialog box which then it
sends it to a report


Sub PrintReports(ReportView As AcView)
' This procedure used in Preview_Click and Print_Click Sub procedures.
' Preview or print report selected in the ReportToPrint option group.
' Then close the Print Sales Reports Dialog form.
Dim strReportName As String
Dim strReportFilter As String
Dim lOrderCount As Long
Dim lOrderCount1 As Long
Dim strReportFilter2 As String
Dim stroffice As String
Dim varitem As Variant


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

Case byMonth
strReportName = "Monthly Sales Report"
lOrderCount = DCountWrapper("*", "Sales Analysis", "[Year]=" &
Me.cbYear & " AND [Month]=" & Me.cbMonth)


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
'TempVars.Add "Display2", DLookupStringWrapper("[Display]", "Sales
Reports", "[Customer No]")



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

Below is what in my monthly report
--------------------------------------------------------------
Public Sub Report_Open(Cancel As Integer)
On Error GoTo ErrorHandler
Dim strSql As String

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


strSql = "SELECT [Year]"
strSql = strSql & ", [Month]"
'strSQL = strSQL & ", [Customer No]"
strSql = strSql & ", ([" & TempVars![Display] & "]) AS
SalesGroupingField" & ",([" & TempVars![Display2] & "]) AS Cust"
strSql = strSql & ", Sum([AmountActual]) AS [Total Sales]" & ",
SUM([Amount1A11F]) AS [1A11F]" & ", Sum([Amount6A6F]) AS [6A6F]"
strSql = strSql & ", first([Sales Analysis].[Posting Date Month]) AS
[Month Name]"
strSql = strSql & " FROM [Sales Analysis] "
strSql = strSql & " Where [Month]=" & TempVars![Month] & " AND [Year]="
& TempVars![Year]
strSql = strSql & " GROUP BY [Year], [Month], [" & TempVars![Group By] &
"];"

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

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


What i need is for a query to be create and shown so i can export to excel
with format, can this be done
 
Alan -

If you want to use one query but change the SQL all the time, then just
create any regular query and give it a name like temp_MonthlySalesReport.
Then in your code you can change the SQL of that query to be the same strSql
you are using in your report. Try something like this:

Set qdf = CurrentDb().QueryDefs("temp_MonthlySalesReport")
qdf.SQL = strSql
qdf.Close
Set qdf = nothing

DoCmd.OpenQuery "temp_MonthlySalesReport", , acReadOnly


--
Daryl S


Alan said:
i have this code which looks up my sales analysis table,
i specify what customer and month i want in my dialog box which then it
sends it to a report


Sub PrintReports(ReportView As AcView)
' This procedure used in Preview_Click and Print_Click Sub procedures.
' Preview or print report selected in the ReportToPrint option group.
' Then close the Print Sales Reports Dialog form.
Dim strReportName As String
Dim strReportFilter As String
Dim lOrderCount As Long
Dim lOrderCount1 As Long
Dim strReportFilter2 As String
Dim stroffice As String
Dim varitem As Variant


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

Case byMonth
strReportName = "Monthly Sales Report"
lOrderCount = DCountWrapper("*", "Sales Analysis", "[Year]=" &
Me.cbYear & " AND [Month]=" & Me.cbMonth)


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
'TempVars.Add "Display2", DLookupStringWrapper("[Display]", "Sales
Reports", "[Customer No]")



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

Below is what in my monthly report
--------------------------------------------------------------
Public Sub Report_Open(Cancel As Integer)
On Error GoTo ErrorHandler
Dim strSql As String

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


strSql = "SELECT [Year]"
strSql = strSql & ", [Month]"
'strSQL = strSQL & ", [Customer No]"
strSql = strSql & ", ([" & TempVars![Display] & "]) AS
SalesGroupingField" & ",([" & TempVars![Display2] & "]) AS Cust"
strSql = strSql & ", Sum([AmountActual]) AS [Total Sales]" & ",
SUM([Amount1A11F]) AS [1A11F]" & ", Sum([Amount6A6F]) AS [6A6F]"
strSql = strSql & ", first([Sales Analysis].[Posting Date Month]) AS
[Month Name]"
strSql = strSql & " FROM [Sales Analysis] "
strSql = strSql & " Where [Month]=" & TempVars![Month] & " AND [Year]="
& TempVars![Year]
strSql = strSql & " GROUP BY [Year], [Month], [" & TempVars![Group By] &
"];"

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

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


What i need is for a query to be create and shown so i can export to excel
with format, can this be done
Click to expand...
 
Back
Top