Code:
Sub PreviewReport_Click()
On Error GoTo Err_PreviewReport_Click
Dim stRepName As String: Rem Holds the Report name
Dim stDispId As String: Rem Holds the Dispenser ID
Dim stQuery As String: Rem Holds the Query name
Dim stWhere As String: Rem Holds the where clause
Dim stExtra As String:
Dim stMonthYear, stYear, stWeekCount As String
Dim intX As Integer, rst As Recordset
Dim dtEndDate As Date
stRepName = Form.cbReports
If IsNull(Form.cbDispenser) Then
MsgBox ("Please pick a Dispenser.")
GoTo Exit_PreviewReport_Click
End If
stDispId = Form.cbDispenser
stWhere = ""
If IsNull(Form.cbMonthYear) Then
MsgBox ("Please pick a Month")
GoTo Exit_PreviewReport_Click
Else
stMonthYear = Form.cbMonthYear
stYear = Format(CDate("1 " & Form.cbMonthYear), "yyyy")
End If
dtEndDate = Nz(DMax("EndDate", "Dispensers", "[ID] = " & stDispId), cLowDate)
If stRepName = "Weekly Dispenser Sales" Then
stQuery = "Weekly Dispenser Sales"
stWhere = "format(START_DT,""MMMM YYYY"") = """ + stMonthYear + """"
ElseIf stRepName = "Individual Weekly Sales" Then: Rem Sheet 8
If dtEndDate <> cLowDate And _
Format(dtEndDate, "YYYYMMDD") < Format(CDate(cbMonthYear), "YYYYMMDD") Then
MsgBox ("There are no records for this Dispenser in this month")
Exit Sub
Else
stQuery = "Individual Weekly Sales"
stWhere = "SALES.DISPENSER_ID = " + stDispId + " and format(START_DT,""MMMM YYYY"") = """ + stMonthYear + """"
End If
ElseIf stRepName = "Company Weekly Sales" Then
stQuery = "Company Weekly Sales"
ElseIf stRepName = "Company Monthly Sales" Then
stQuery = "Company Monthly Sales"
ElseIf stRepName = "Company Year End" Then
stQuery = "Company Year End"
stWhere = "SALES.WorkingWeek=-1"
ElseIf stRepName = "Dispenser Comparison Monthly" Then: Rem Sheet 11
stQuery = "Dispenser Comparison Monthly"
ElseIf stRepName = "Dispenser Comparison Yearly" Then
stQuery = "Dispenser Comparison Yearly"
stWhere = "Year= " & stYear & " and SALES.WorkingWeek =-1"
ElseIf stRepName = "Individual Monthly Sales" Then
If dtEndDate <> cLowDate And _
Format(dtEndDate, "YYYY") < Format(CDate(cbMonthYear), "YYYY") Then
MsgBox ("There are no records for this Dispenser in this year")
Exit Sub
Else
stQuery = "Individual Monthly Sales"
stWhere = "DISPENSER_ID=" & stDispId
End If
ElseIf stRepName = "Individual Year End" Then
stQuery = "Individual Year End"
stWhere = "DISPENSERID=" & stDispId & " and SALES.WorkingWeek = -1"
End If
DoCmd.OpenReport stRepName, acPreview, stQuery, stWhere
Exit_PreviewReport_Click:
Exit Sub
Err_PreviewReport_Click:
MsgBox Err.Description
Resume Exit_PreviewReport_Click
End Sub
one of the options for the report is to pick a report type, the pick the dispenser, the the month
so for example INDIVDUALmonthl ey SALES , JOE BLOGS for the month of JUNE.
so i would like it to look in the weekly sales report, extract the sales for JOE BLOGS for the month of june to be displayed.
below is the query im trying to use to extract the information
Code:
SELECT DISTINCTROW SALES.DISPENSER_ID, DISPENSERS.FORENAME + " " + DISPENSERS.SURNAME AS Dispenser, format(START_DT,"mmmm") AS [Month], format(START_DT,"yyyy") AS [Year], Sum(SALES.WorkingWeek)*-1 AS NoOfWeeks, count(format(START_DT,"mmmm yyyy")) AS PETE, Sum(SALES.SALES) AS TotalSales, Sum(SALES.SALES_UNITS) AS TotalSalesUnits, Sum(SALES.PHILIPS) AS TotalPhilips, iif(Sum(Sales)=0,0,Sum(DONE)/Sum(SALES)) AS SellingAppDone, iif(Sum(Sales)=0,0,Sum(BOOKED)/Sum(SALES)) AS SellingAppBooked, iiF(Sum(SALES_UNITS)=0,0,Sum(SALES_VALUE)/Sum(SALES_UNITS)) AS AvgPrice, Sum(SALES_VALUE)/Sum(SALES.WorkingWeek)*-1 AS AvgSalesPerWeek, IIF(Sum(SALES_UNITS)-Sum(SALES)=0,0,Sum(SALES)/(Sum(SALES_UNITS)-Sum(SALES))) AS BinauralSales, Sum(SALES_VALUE) AS TotalSaleValue, null AS LessCancelled, Sum(FITTED_UNITS) AS FittedUnits, Sum(FITTED_VALUE) AS FittedValue, null AS LessCancel, Sum(DONE) AS AppDone, Sum(BOOKED) AS AppBooked, Sum(DONE)/Sum(SALES.WorkingWeek)*-1 AS AvgPerWeekDone, Sum(BOOKED)/Sum(SALES.WorkingWeek)*-1 AS AvgPerWeekBooked, cDate('1 ' & format(START_DT,"mmmm") & " " & format(START_DT,"yyyy") ) AS OrdDate
FROM PERIOD INNER JOIN (DISPENSERS INNER JOIN SALES ON DISPENSERS.ID = SALES.DISPENSER_ID) ON PERIOD.ID = SALES.PERIOD_ID
GROUP BY SALES.DISPENSER_ID, DISPENSERS.FORENAME, DISPENSERS.SURNAME, format(START_DT,"mmmm"), format(START_DT,"yyyy")
ORDER BY SALES.DISPENSER_ID, cDate('1 ' & format(START_DT,"mmmm") & " " & format(START_DT,"yyyy") ), cDate('1 ' & format(START_DT,"mmmm") & " " & format(START_DT,"yyyy") );
but im getin a parmeter box which is asking for me to enter parameter value cDate('1 ' & format(START_DT ,"mmmm") & " " & format(START_DT ,"yyyy") ) ?? i have no idea why.
i get parmeter boxes when i try and preview alot of the reports........ .........its so confusing, i dont kno were to start looking
Comment