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:

stWhereConditio n = Report.rptRMSta tus.RecordSourc e = "qryRMStatu s1"

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

Anyway, it should look something like this:

stWhereConditio n="([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.OpenRepor t 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 stWhereConditio n 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