Solved

Open report using multiple criteria from a selection form

Posted on 2014-02-05
6
1,367 Views
Last Modified: 2014-02-06
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
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39837541
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

0
 

Author Comment

by:SteveL13
ID: 39838742
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

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39838822
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?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:SteveL13
ID: 39838971
If I leave all four blank I get nothing on the report.  Customer name is text.   I attached a sample.
Example.accdb
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39839379
In the code I gave you, you need to replace txtTo and txtFrom with the actual names of your date textboxes (txtStartDate and txtEndDate):

    dtTo = Nz(txtEndDate, #1/1/2100#)
    dtFrom = Nz(txtStartDate, #1/1/1900#)

Open in new window

0
 

Author Closing Comment

by:SteveL13
ID: 39839616
Perfect again!  Thank you.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question