Parameter box when try to preview report

[DavidOwens]

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
 

hey guys the above code is for a preview report button on a main menu.

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

 
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

[Rabbit]

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.

That's because you're trying to sort by a field that doesn't exist. If you look at the SORT BY statement of your SQL you will see 2 calculated fields that don't exist and hence you can't sort by them.

[DavidOwens]

That's because you're trying to sort by a field that doesn't exist. If you look at the SORT BY statement of your SQL you will see 2 calculated fields that don't exist and hence you can't sort by them.

so take the ORDER BY off and it should work,
. but would that let me pick a report, chose a dispenser by say 12/05/02-17/05/02 and will display the sales for that period?

[DavidOwens]

so take the ORDER BY off and it should work,
. but would that let me pick a report, chose a dispenser by say 12/05/02-17/05/02 and will display the sales for that period?

no adivce?

[Rabbit]

so take the ORDER BY off and it should work,
. but would that let me pick a report, chose a dispenser by say 12/05/02-17/05/02 and will display the sales for that period?

Filtering for a records within a range of dates is never done through the ORDER BY as ORDER BY only sorts the records returned. If you want to filter for a date range you do it through the WHERE clause.

[DavidOwens]

Filtering for a records within a range of dates is never done through the ORDER BY as ORDER BY only sorts the records returned. If you want to filter for a date range you do it through the WHERE clause.

ideas? please

[Rabbit]

Well, to filter for a range of dates it would be WHERE #Date1# AND #Date2#.