month will not change

[DavidOwens]

SELECT DISTINCTROW Format(PERIOD.START_DT,"mmmm yyyy") AS MonthYear, SALES.PERIOD_ID, Sum(SALES.SALES) AS SumOfSALES, Sum(SALES.SALES_UNITS) AS SumOfSALES_UNITS, Sum(SALES.SALES_VALUE) AS SumOfSALES_VALUE, Sum(SALES.PHILIPS) AS SumOfPHILIPS, Sum(SALES.FITTED) AS SumOfFITTED, Sum(SALES.FITTED_UNITS) AS SumOfFITTED_UNITS, Sum(SALES.FITTED_VALUE) AS SumOfFITTED_VALUE, Sum(SALES.DONE) AS SumOfDONE, Sum(SALES.BOOKED) AS SumOfBOOKED, Sum(SALES.BOOKED_NEXT) AS SumOfBOOKED_NEXT, PERIOD.END_DT, PERIOD.START_DT, Min(SALES.WorkingWeek)*-1 AS WorkingWeek
FROM PERIOD INNER JOIN SALES ON PERIOD.ID = SALES.PERIOD_ID
GROUP BY Format(PERIOD.START_DT,"mmmm yyyy"), SALES.PERIOD_ID, PERIOD.START_DT, PERIOD.END_DT;

high guys, hey above code is meant to grab the contents of a COMPANY weekly SALES, but when i change the month from the main menu,when the preview report button is pushed and the report is shown, the month does not change, it stays on AUGUST 2001 for some reason, displaying the weeks in that month.

any ideas?

preview button 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

[Rabbit]

Put a break at the beginning of the code and step through (F8) to see if your variables are returning the right results.

[MMcCarthy]

I think your problem lies in your date formats.

For instance ... what does this return?

stYear = Format(CDate("1 " & Form.cbMonthYear), "yyyy")

Mary