Where cluase of OpenReport

  • Thread starter Thread starter Dave the wave
  • Start date Start date
D

Dave the wave

I'm trying to open a report using the DoCmd OpenReport command and by
specifying the WHERE clause of the command using an open form's filter
property. The filter property looks like: ((fieldname="value to match")),
but when I run the procedure a parameter dialog box appears asking for a
value for fieldname.

Any suggestions as to what the problem might be?
 
Dave the wave said:
I'm trying to open a report using the DoCmd OpenReport command and by
specifying the WHERE clause of the command using an open form's filter
property. The filter property looks like: ((fieldname="value to
match")), but when I run the procedure a parameter dialog box appears
asking for a value for fieldname.

Any suggestions as to what the problem might be?
Click to expand...

Have you verified that the field "fieldname" is included in the report's
recordsource?
 
Dirk:
Yes. I am actually getting the fieldname from the subform's filter property.
(The subform allows the user to right-click to "filter by selection". So,
both the fieldname and the value are being read from the subform's filter
property value.)

Here's the code I've been working with:

Private Sub Command35_Click()
If Not (Me.sfrmIReview.Form.FilterOn) Then
Exit Sub
End If
Debug.Print sfrmIReview.Form.Filter

stDocName = "rptActiveHoldsByEmployee"
DoCmd.OpenReport stDocName, acPreview, , Me.sfrmIReview.Form.Filter

End Sub

This is the output from the Debug.Print sfrmIReview.Form.Filter:

((qryRepackReview.ProductsDescription="HDL Citrus Breeze"))

I've tried replacing Me.sfrmIReview.Form.Filter with an actual string -like
"qryRepackReview.ProductsDescription='HDL Citrus Breeze'"-
but I get the same result, the enter parameter dialog window.

Thanks for your help. I hope you see something in my code that explains my
problem.
 
Dave the wave said:
Dirk:
Yes. I am actually getting the fieldname from the subform's filter
property. (The subform allows the user to right-click to "filter by
selection". So, both the fieldname and the value are being read from
the subform's filter property value.)

Here's the code I've been working with:

Private Sub Command35_Click()
If Not (Me.sfrmIReview.Form.FilterOn) Then
Exit Sub
End If
Debug.Print sfrmIReview.Form.Filter

stDocName = "rptActiveHoldsByEmployee"
DoCmd.OpenReport stDocName, acPreview, ,
Me.sfrmIReview.Form.Filter

End Sub

This is the output from the Debug.Print sfrmIReview.Form.Filter:

((qryRepackReview.ProductsDescription="HDL Citrus Breeze"))

I've tried replacing Me.sfrmIReview.Form.Filter with an actual string
-like "qryRepackReview.ProductsDescription='HDL Citrus Breeze'"-
but I get the same result, the enter parameter dialog window.

Thanks for your help. I hope you see something in my code that
explains my problem.
Click to expand...

What is the recordsource of the report? If it's a query, please post
the SQL of that query. My guess is that the recordsource either doesn't
include a field named "ProductsDescription", or if it does, that field
doesn't come from qryRepackReview.
 
Dirk:
Here is the query SQL:
SELECT Repacks.RepackID, Repacks.RepackStatus, Repacks.RepackNumber, Repacks.RepackDate, Products.ProductsDescription, Defects.DefectName, Employees.EmployeeName, Repacks.RepackRCARequired, Repacks.Repack5YRequired, Repacks.RepackTotalCases
FROM Employees INNER JOIN (Defects INNER JOIN (Products INNER JOIN Repacks ON Products.ProductID = Repacks.ProductID) ON Defects.DefectID = Repacks.DefectID) ON Employees.EmployeeID = Repacks.EmployeeID
WHERE (((Repacks.RepackStatus)=0))
ORDER BY Repacks.RepackNumber;

The query does contain a ProductsDescription. I should note that the parameter asked for changes depending on what field has been filtered. If I "filtered by selection" on employees, the parameter dialog window would ask for a value for Employees instead of ProductDescription.

The where clause in the query filters out closed investigations so that only active investigations are included in the record set.

Again, thank you for your time and attention.
 
Is that query named "qryRepackReview"? The filter you are trying to
apply specifically refers to qryRepackReview.ProductsDescription, and I
don't see a query named "qryRepackReview" anywhere in the SQL you
posted. As an experiment, try hard-coding this filter instead:

ProductsDescription="HDL Citrus Breeze"
 
Back
Top