Where condition on DoCmd.OpenReport

[surfside1]

I've built a report using the report wizard with the table containing all records. I want to use an If statement prior to the DoCmd.OpenReport that will open the report for the records that match the value of a field in the table through the where condition and I just can't get the syntax correct. Should I use a query instead as the data for the report - and can I if the report was built using the table? I'd appreciate some code examples for the Where or let me know if I can use an query for this report that was built based on a table. Appreciate your help.

 

[PHV]

Did you use the 4th argument of the DoCmd.OpenReport method ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[surfside1]

Yes, but it doesn't like the Select statement (red). Also I tried to remove the 3rd " at the end and it keeps adding it back:
Private Sub combo_Available_Reports_Click()
Dim str_Report_Selected As String
Dim str_Where As String
Me.combo_Available_Reports.SetFocus
str_Report_Selected = Me.combo_Available_Reports
If str_Report_Selected = "Closed Listing" Then
str_Where = "SELECT tbl_Listings.ID, _
tbl_Listings.Item_Nbr, _
tbl_Listings.Status, _
tbl_Listings.End_Date, _
tbl_Listings.Product_Type, _
tbl_Listings.Product_Cost, _
tbl_Listings.Ebay_Fees, _
tbl_Listings.Pay_Pal_Fees, _
tbl_Listings.Total_Cost, _
tbl_Listings.Shipping_Profit, _
tbl_Listings.Winning_Bid_Profit_Amount, _
tbl_Listings.Winning_Bid_Profit_Percent, _
tbl_Listings.Loss_Amount _
FROM tbl_Listings
WHERE tbl_Listings.Status = "2 - Sold"""
' "Select * from tbl_Listings Where Status = '2 - Sold'" (this is what I tried first)
DoCmd.OpenReport "rpt_Listings", acViewPreview, , str_Where

End If

 

[PHV]

As clearly explained in the VBA help, the 4th argument of the OpenReport method is a WHERE clause without the keyword WHERE.
So, the RecordSource of rpt_Listings should be tbl_Listings (or a Query based on tbl_Listings) and the criteria:

str_Where = "tbl_Listings.Status = '2 - Sold'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[surfside1]

PH - I've always had problems with the where condition without the where! Thanks - I knew you were one of the best since you first helped me 2 yrs ago! Surfside1