Multiple filter Report (1 Viewer)

[hfsitumo2001]

I tried to have multiple filter of report by date and by customer, but when I run the filter form, it won'r work, It prompts me to fill in the order date. In the query I already put the filter form on both fields, order date and customer. Can anybody help why it can not filter for both criteria?
attached is my filter form

 

[theDBguy]

I think it would be better if you could attach your db, so we can take a look.

 

[MajP]

If the query works by itsels, then check the report properties. Sometimes when designing you get values stuck in the Filter and OrderBy properties. If not follow theDBguy and post.

 

[hfsitumo2001]

I think it would be better if you could attach your db, so we can take a look.

Dbguy, I do not know which one to trims, because it was already to much, so I can not post it. Can you see the design query here below. Can the filter simultaneously filter together?

 

[MajP]

IF you go to the query designer and go into SQL view, post that. It will be easier to read.

 

[hfsitumo2001]

IF you go to the query designer and go into SQL view, post that. It will be easier to read.

SELECT SalesOrder.CustomerID, Customers.Customer, SalesOrder.SlsControlNo, SalesOrder.IssuedDate, SalesOrderDetails.ItemCode, Inventory.Description, SalesOrderDetails.Notes, SalesOrderDetails.UnitPrice, Inventory.UOM, SalesOrderDetails.Quantity, [SalesOrderDetails].[UnitPrice]*[Quantity] AS NetAmount
FROM Customers INNER JOIN (Inventory INNER JOIN (SalesOrder INNER JOIN SalesOrderDetails ON SalesOrder.[ID] = SalesOrderDetails.[SalsOrderID]) ON Inventory.[ItemCode] = SalesOrderDetails.[ItemCode]) ON Customers.CustomerID = SalesOrder.CustomerID
WHERE (((Customers.Customer)=[Forms]![frmReportselection]![Combo_user]) AND ((SalesOrder.IssuedDate) Between [Forms]![frmReportselection]![txtstDate] And [forms]![frmReportselection]![txtEndate]));

That is the SQL code MajP, as you see I put 2 filters, one for Customer and one for Date start and Date End,
Usually can we make to filters

 

[MajP]

To eliminate anything with the report. Can you open the form (if modal is true then set it to false), fill in the values, and then open that query. Does the query give the prompts? Also can you double check the names of the txtStDate and txtEnDate? Sometimes we copy things and the names are not what we expect.
I formatted the query to make easier to read.

SELECT salesorder.customerid,
       customers.customer,
       salesorder.slscontrolno,
       salesorder.issueddate,
       salesorderdetails.itemcode,
       inventory.description,
       salesorderdetails.notes,
       salesorderdetails.unitprice,
       inventory.uom,
       salesorderdetails.quantity,
       [salesorderdetails].[unitprice] * [quantity] AS NetAmount
FROM   customers
       INNER JOIN (inventory
                   INNER JOIN (salesorder
                               INNER JOIN salesorderdetails
                                       ON salesorder.[id] =
                                          salesorderdetails.[salsorderid])
                           ON inventory.[itemcode] =
                              salesorderdetails.[itemcode])
               ON customers.customerid = salesorder.customerid
WHERE  ( ( ( customers.customer ) =
           [forms] ! [frmreportselection] ! [combo_user] )
         AND ( ( salesorder.issueddate ) BETWEEN
               [forms] ! [frmreportselection] ! [txtstdate]
                   AND
                     [forms] ! [frmreportselection] ! [txtendate] ) );

 

[hfsitumo2001]

To eliminate anything with the report. Can you open the form (if modal is true then set it to false), fill in the values, and then open that query. Does the query give the prompts? Also can you double check the names of the txtStDate and txtEnDate? Sometimes we copy things and the names are not what we expect.
I formatted the query to make easier to read.

SELECT salesorder.customerid,
       customers.customer,
       salesorder.slscontrolno,
       salesorder.issueddate,
       salesorderdetails.itemcode,
       inventory.description,
       salesorderdetails.notes,
       salesorderdetails.unitprice,
       inventory.uom,
       salesorderdetails.quantity,
       [salesorderdetails].[unitprice] * [quantity] AS NetAmount
FROM   customers
       INNER JOIN (inventory
                   INNER JOIN (salesorder
                               INNER JOIN salesorderdetails
                                       ON salesorder.[id] =
                                          salesorderdetails.[salsorderid])
                           ON inventory.[itemcode] =
                              salesorderdetails.[itemcode])
               ON customers.customerid = salesorder.customerid
WHERE  ( ( ( customers.customer ) =
           [forms] ! [frmreportselection] ! [combo_user] )
         AND ( ( salesorder.issueddate ) BETWEEN
               [forms] ! [frmreportselection] ! [txtstdate]
                   AND
                     [forms] ! [frmreportselection] ! [txtendate] ) );

Thank you MajP, yes there is prompt to fill order date, while I do not have order date field in my query. Do you think because the same name of the txtStDate and txtEnDate?,in the other filter that we copy, then it prompt me?

 

[arnelgp]

you can simply use a Query without Referencing Any Form control:

SELECT SalesOrder.CustomerID, Customers.Customer, SalesOrder.SlsControlNo, SalesOrder.IssuedDate, SalesOrderDetails.ItemCode, Inventory.Description, SalesOrderDetails.Notes, SalesOrderDetails.UnitPrice, Inventory.UOM, SalesOrderDetails.Quantity, [SalesOrderDetails].[UnitPrice]*[Quantity] AS NetAmount
FROM Customers INNER JOIN (Inventory INNER JOIN (SalesOrder INNER JOIN SalesOrderDetails ON SalesOrder.[ID] = SalesOrderDetails.[SalsOrderID]) ON Inventory.[ItemCode] = SalesOrderDetails.[ItemCode]) ON Customers.CustomerID = SalesOrder.CustomerID
WHERE (((Customers.Customer)=[cb1]) AND ((SalesOrder.IssuedDate) Between [dt1] And [dt2]));


on the Click event of the button you SetParameter to the Report you want to open:

private sub button1_click()
with docmd
    .setparameter "cb1", Me![Combo_user]
    .setparameter "dt1", "#" & Format$(Me![txtstDate], "mm\/dd\/yyyy") & "#"
    .setparameter "dt2", "#" & Format$(Me![txtstDate], "mm\/dd\/yyyy") & "#"
    .openreport "reportName", acViewPreview
end with
end sub

this makes your Query flexible and can be consumed by any form/report, etc.

 

[hfsitumo2001]

you can simply use a Query without Referencing Any Form control:

SELECT SalesOrder.CustomerID, Customers.Customer, SalesOrder.SlsControlNo, SalesOrder.IssuedDate, SalesOrderDetails.ItemCode, Inventory.Description, SalesOrderDetails.Notes, SalesOrderDetails.UnitPrice, Inventory.UOM, SalesOrderDetails.Quantity, [SalesOrderDetails].[UnitPrice]*[Quantity] AS NetAmount
FROM Customers INNER JOIN (Inventory INNER JOIN (SalesOrder INNER JOIN SalesOrderDetails ON SalesOrder.[ID] = SalesOrderDetails.[SalsOrderID]) ON Inventory.[ItemCode] = SalesOrderDetails.[ItemCode]) ON Customers.CustomerID = SalesOrder.CustomerID
WHERE (((Customers.Customer)=[cb1]) AND ((SalesOrder.IssuedDate) Between [dt1] And [dt2]));


on the Click event of the button you SetParameter to the Report you want to open:

private sub button1_click()
with docmd
    .setparameter "cb1", Me![Combo_user]
    .setparameter "dt1", "#" & Format$(Me![txtstDate], "mm\/dd\/yyyy") & "#"
    .setparameter "dt2", "#" & Format$(Me![txtstDate], "mm\/dd\/yyyy") & "#"
    .openreport "reportName", acViewPreview
end with
end sub

this makes your Query flexible and can be consumed by any form/report, etc.

Thank you Arnel, I will try to understand first. But we still need the form right?

 

[arnelgp]

you need the form that's why i added the code for you button's click event.

 

[hfsitumo2001]

you need the form that's why i added the code for you button's click event.

Arnel, thank you very much, it works like a chant, you are awesome. But how can we make if we live the date blank, only combo, it show from the inception to date.

Thank you

 

[hfsitumo2001]

you need the form that's why i added the code for you button's click event.

Arnel, in fact still can not work the filter. The report shown with no record

 

[hfsitumo2001]

If the query works by itsels, then check the report properties. Sometimes when designing you get values stuck in the Filter and OrderBy properties. If not follow theDBguy and post.

MajP, I have checked report property on data... Order by on load : Yes and Allow fiters yes. Is that ok?

 

[hfsitumo2001]

you need the form that's why i added the code for you button's click event.

Arnel, I do not know what's wrong from my combobox which takes data from customer table, and I just need the name. this is the source of the combobox: SELECT DISTINCT Customers.CustomerID, Customers.Customer FROM Customers ORDER BY Customer;

 

[arnelgp]

please post all your code in the form.

 

[MajP]

I will let @arnelgp address your issues. But to answer your two questions

The reason I suggested to look into the report Order By or Filter properties is because if you may have something accidently left in there like

Order By: [OrderDate]
or
Filter: [OrderDate] = #1/1/2020#

Then when your report opens it will prompt you for a value since it cannot find orderdate anywhere. This is a common cause of getting an unknown prompt.

I was also trying to get you to run the query that references the form by itself. If the query works you know the issue is with the report. If the query does not work then you know it is the query.

 

[hfsitumo2001]

please post all your code in the form.

This is the on click of the button:

Private Sub OpenSelectedUsagerpt_Click()
With DoCmd
    .SetParameter "cb1", Me![Combo_User]
    .SetParameter "dt1", "#" & Format$(Me![txtstDate], "mm\/dd\/yyyy") & "#"
    .SetParameter "dt2", "#" & Format$(Me![txtEndate], "mm\/dd\/yyyy") & "#"
    .OpenReport "SuppliesUsagebyDepartment_Users_MultiSelectTest", acViewPreview
End With
End Sub

This is the row source of the combo:

SELECT DISTINCT Customers.CustomerID, Customers.Customer FROM Customers ORDER BY Customer;

This is the name of the combo box: Combo_User
This is the name of the text box for startdate: txtstDate
This is the name of the textbox for End Date: txtEndate

 

[arnelgp]

if dates are missing, you add Nz() to each textbox (date).
also you need to make sure that Combo_User is not blank:

Private Sub OpenSelectedUsagerpt_Click()
If Me![Combo_User].ListIndex > -1 Then
    With DoCmd
        .SetParameter "cb1", Me![Combo_User]
        .SetParameter "dt1", "#" & Format$(Nz(Me![txtstDate], #1/1/1899#), "mm\/dd\/yyyy") & "#"
        .SetParameter "dt2", "#" & Format$(Nz(Me![txtEndate], #1/1/9999#), "mm\/dd\/yyyy") & "#"
        .OpenReport "SuppliesUsagebyDepartment_Users_MultiSelectTest", acViewPreview
    End With
Else
    Msgbox "Please select a user from the list."
End If
End Sub

also how did you Instantiate the ClassAutoCompleteCombo.
can you show me that Load/Open event of your form.

 

[hfsitumo2001]

please post all your code in the form.

What is this I found on open property of the report. Will it effect?

Private Sub Report_Open(Cancel As Integer)
DoCmd.Restore
End Sub