Solved

Open report using multiple criteria from a selection form

Posted on 2014-02-05
6
1,376 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

738 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