Filter Not Working

[BigCat]

I am using a form so that the user can enter a date range. That date range is then captured and is supposed to be used to filter the data in a query that is pulling the needed fields from a large main table in the database. The query results that are in that date range should be the only records in the report, but right now the filter does not appear to be working because all of the records from my query are showing up in the report.

Private Sub cmdPreview_Click()
    On Error GoTo Err_Handler      
 
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim reportView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
    
    'DO set the values in the next 3 lines.
    strReport = "Monthly Production Summary"      'Put your report name in these quotes.
    strDateField = "[MFG COMP]" 'Put your field name in the square brackets in these quotes.
    lngView = acViewPreview     'Use acViewNormal to print instead of preview.
    
    'Build the filter string.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " <= " & Format(Me.txtEndDate, strcJetDate) & ")"
    End If
    
    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    
    'Open the report.
    'Debug.Print strWhere        'Remove the single quote from the start of this line for debugging purposes.
    DoCmd.OpenReport strReport, lngView, strWhere
Exit_Handler:
    Exit Sub
Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
End Sub

[RuralGuy]

Start by moving your argument to the WhereCondition argument slot which is the 4th parameter.
DoCmd.OpenReport strReport, lngView, , strWhere

[BigCat]

When I do that I get Run-Time Error: 30025 'Invalid SQL Statement. Check the Server filter on the form record source. when it tries to execute the openreport command

[RuralGuy]

Add a MsgBox "[" & strWhere & "]" just before you run the report and look at the string for validity.

[BigCat]

I get in the message box. [([MFG COMP] >= #05/01/2011#) AND ([MFG COMP] <= #05/31/2011#)]

[RuralGuy]

That looks like a valid WhereCondition argument. Shorten it to just the >= part and see if it still barfs.

[BigCat]

I still get the same error even after commenting out the second half of the wherecondition

[RuralGuy]

Change it to absolute [MFG COMP] = #05/01/2011#
Is the [MFG COMP] field a DateTime field or a text field?

[BigCat]

It is a datetime field and I tried making it an absolute and I got the same error, but in the end I still need it to be a date range I can't use absolutes

[RuralGuy]

I'm not sure what you are doing. Can you copy and paste the OpenReport line you are using so we can see it?

[BigCat]

DoCmd.OpenReport strReport, lngView, , strWhere

[RuralGuy]

Try opening the Report in Design view and see if anything strange is in the Filter property. See if the Report runs by itself.

[BigCat]

I made that change and it opens in design view and then if I switch over to report view nothing has been filtered it has every record from my query in it, and I just want records from the month given in the date range

[RuralGuy]

Any chance you can post your db zipped up? We're going around in circles. Do a Compact and Repair first and remove any sensitive data.