asked on

Open report using multiple criteria from a selection form

Please see attached.

I am trying to figure out how to open a report using multiple criteria from a selection form.

If the user leaves any of the 4 top field blank then they get all of the records as defined by the option group. The code I have in place for the option group thanks to another expert is:

Private Sub Frame21_Click()

Select Case Frame21

Case 1
DoCmd.OpenReport "rptOpenReturns", acViewPreview, , "Closed = False", acWindowNormal
Reports!rptOpenReturns.lblOpenRMAStatus.Visible = True
Me.Frame21 = Null

Case 2
DoCmd.OpenReport "rptOpenReturns", acViewPreview, , "Closed = True", acWindowNormal
Reports!rptOpenReturns.lblClosedRMAStatus.Visible = True
Me.Frame21 = Null

Case 3
DoCmd.OpenReport "rptOpenReturns", acViewPreview
Reports!rptOpenReturns.lblAllRMAStatus.Visible = True
Me.Frame21 = Null

End Select

End Sub

But if they, for example, select a customer from the 1st combobox then they get only the records pertaining to that customer AND the criteria as selected in the option group.

If they select a customer from the 1st combobox AND enter a date range then they get only the records pertaining to that customer AND Date Range AND the criteria as selected in the option group.

I hope this make sense. I just don't know if it can be done.

--Steve
SelectionForm.jpg

mbizup

Try this, making the appropriate substitutions for your own field names:

Private Sub Frame21_Click()
    Dim strCust as string
    dim dtFrom as Date
    dim dtTo as date
    dim strPN as string
    dim strCrit as string

    strCust = NZ(cboCust, "*")
   strPN = NZ(cboPN, "*")
   dtTo = NZ(txtTo, #1/1/2100#)
   dtFROM = NZ(txtFrom, #1/1/1900#)

    strCrit = "Customer LIKE '" & strCust & "' AND [PartNumber] LIKE '" & strPN & "' AND [YourDateField] BETWEEN #" & dtFrom & "# AND #" & dtTo & "#"
    Select Case Frame21
    
        Case 1
        DoCmd.OpenReport "rptOpenReturns", acViewPreview, , "Closed = False AND " & strCrit, acWindowNormal
        Reports!rptOpenReturns.lblOpenRMAStatus.Visible = True
        Me.Frame21 = Null

        Case 2
        DoCmd.OpenReport "rptOpenReturns", acViewPreview, , "Closed = True AND " & strCrit, acWindowNormal
        Reports!rptOpenReturns.lblClosedRMAStatus.Visible = True
        Me.Frame21 = Null

        Case 3
        DoCmd.OpenReport "rptOpenReturns", acViewPreview,, strCrit
        Reports!rptOpenReturns.lblAllRMAStatus.Visible = True
        Me.Frame21 = Null
        
    End Select

End Sub

Open in new window

SteveL13

ASKER

I must be doing something wrong. I replace field names with the real names but when I try to run the report(s) I get nothing. Just an empty report. Here is what I have:

    Dim strCust As String
    Dim dtFrom As Date
    Dim dtTo As Date
    Dim strPartN As String
    Dim strCrit As String

    strCust = Nz(cboCustomer, "*")
    strPartN = Nz(cboPartN, "*")
    dtTo = Nz(txtTo, #1/1/2100#)
    dtFrom = Nz(txtFrom, #1/1/1900#)

    strCrit = "Customer_1 LIKE '" & strCust & "' AND [Part_No] LIKE '" & strPartN & "' AND [Date] BETWEEN #" & dtFrom & "# AND #" & dtTo & "#"
    Select Case Frame21
    
        Case 1
        DoCmd.OpenReport "rptOpenReturns", acViewPreview, , "Closed = False AND " & strCrit, acWindowNormal
        Reports!rptOpenReturns.lblOpenRMAStatus.Visible = True
        Me.Frame21 = Null

        Case 2
        DoCmd.OpenReport "rptOpenReturns", acViewPreview, , "Closed = True AND " & strCrit, acWindowNormal
        Reports!rptOpenReturns.lblClosedRMAStatus.Visible = True
        Me.Frame21 = Null

        Case 3
        DoCmd.OpenReport "rptOpenReturns", acViewPreview, , strCrit
        Reports!rptOpenReturns.lblAllRMAStatus.Visible = True
        Me.Frame21 = Null
        
    End Select

Open in new window

mbizup

Try running with the four criteria left blank on the form. Do you get all records?

What are the datatypes invovled?

Is the Customer Name (text) stored, or a numeric Customer ID?

Can you post a sample?

SteveL13

ASKER

If I leave all four blank I get nothing on the report. Customer name is text. I attached a sample.
Example.accdb

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SteveL13

ASKER

Perfect again! Thank you.