Filter Query through Form

[Pookaroo85]

SET-UP: I currently have a form with unbound text fields to type in the criteria. Then, I have a query with the criteria referencing those text fields. On the form, I have a 'Report' command button to print preview the report.

GOAL: I have a form that filters a query to then print a report. There are two sections in the form: one for Start Date and End Date, the other for a material number. I would like the option to do the following scenarios:

• Enter a date range to report all materials received within that range.
• Enter a material number to report all material receipts.
• Enter a date range and material number to report a specific material received within a certain period.
• Leave all three fields blank and report all receipts of every material.

[Pookaroo85]

This is what I've done... I now have 4 quearies for my 4 different scenarios. I need to change the record source of the report based on what fields are null in my form. Theoretically, I think this should work, but it doesn't and I'm not skilled enough to know why. Any help would be appreciated.

Private Sub cmdReport_Click()
On Error GoTo Err_cmdReport_Click
 
    Dim stDocName As String
    Dim stWhereCondition As String
 
    stDocName = "rptRMStatus"
 
If IsNull(Me.IDH.Value) Then
    stWhereCondition = Report.rptRMStatus.RecordSource = "qryRMStatus1"
ElseIf IsNull(Me.StartDate.Value) And (Me.EndDate.Value) Then
    stWhereCondition = Report.rptRMStatus.RecordSource = "qryRMStatus2"
ElseIf IsNull(Me.IDH.Value) And (Me.StartDate.Value) And (Me.EndDate.Value) Then
    stWhereCondition = Report.rptRMStatus.RecordSource = "qryRMStatus4"
Else: stWhereCondition = Report.rptRMStatus.RecordSource = "qryRMStatus3"
End If
 
DoCmd.OpenReport stDocName, acViewPreview, , stWhereCondition, acWindowNormal
 
Exit_cmdReport_Click:
    Exit Sub
 
Err_cmdReport_Click:
    MsgBox Err.Description
    Resume Exit_cmdReport_Click
 
End Sub
 

[dsatino]

This makes no sense to the machine:

stWhereCondition = Report.rptRMStatus.RecordSource = "qryRMStatus1"

I'm surprised it doesn't throw an error.

Anyway, it should look something like this:

stWhereCondition="([YourDateField] Between #1/1/2014# AND #1/31/2014#) AND [YourMatNoField]=12345"

The bracketed items refer to fields in the report recordsource.

[jforbes]

Hey Pookaroo85,

You are on the right track. Typically, when opening a Report in this manner, the Reports RecordSource is not changed. The RecordSource is setup to return a all records then when the Report is opened with the DoCmd.OpenReport method, a WhereCondition is supplied to filter the Report down to only the Records that are desired. It's basically splitting your Query into two parts, the part that selects the fields is what the Report is saved with, while the part that selects which rows is supplied by the Form when the Report is opened.

So what you will want to do is set the RecordSource of your report to a Query that you have made that returns all records(the last bullet point from Post #1), then build up the stWhereCondition string to filter Report.

This is an Example that should be close to what you need, but you will need to tweak it:

If Len(Nz(Me.IDH.Value, "")) > 0 Then
    stWhereCondition = stWhereCondition & "IDH='" & Me.IDH.Value & "'"
End If
If IsDate(Me.StartDate.Value) AND IsDate(Me.EndDate.Value) THEN
   If Len(stWhereCondition) > 0 THEN stWhereCondition = stWhereCondition & " AND "
   stWhereCondition = stWhereCondition & "[NameOfDateField] Between #" & Me.StartDate.Value & "# AND #" & Me.EndDate.Value & "#)"
End If