How to filter Datamember for datareport

Hi

Open a recordset using an Sql expresion with the were clause and set it to the datareport as following. You can set the filter property to the recordset or the datasource of the datareport

Dim Rs As Recordset
Dim SqlStr As String

SqlStr = "SELECT STATEMENT WHERE ID = 10"

Rs.Open SqlStr, Connection ' <-- Open it in your correct way

Set DataReport1.DataSource = Rs

DataReport1.Refresh

Thanks for the response.  I thought that was it but I am getting an error.  I replaced 'Statement' with 'TestPriceRpt' am using:

  sqlString = "SELECT TestPriceRpt WHERE ClientID =  16"
 
  rs.Open sqlString, cn

'TestPriceRpt' is the datamamber that I define in DataEnvironment1 using an sql statement.  When I set up the report I used DataEnvironment1 as the datasource and 'TestPriceRpt' as the datamemeber.

I am getting the error 'Missing operator in query expression'

I changed the sql to be   sqlString = "SELECT * from TestPriceRpt WHERE ClientID =  " & 16

now I am getting 'The Microsoft Jet Database engine cannot find the input table or query TestPriceRpt.

What am I missing?  Can I reference a Command (TestPriceRpt) set up in the DataEnvironment in an sql statement?  If not I don't understand your answer.

The Sql String must be like this:

SELECT [ColumnName] FROM [TableName] WHERE [ColumnName] = [Value]

I think you should check your tablename

Sorry for the confusion.  I apprecaite your feedback but as I mentioned above, 'TestPriceRpt' is not a database table in the MDB nor doesit exist as a query in the MDB.  I create it in the VB6.0 dataenvironment using SQL commands.  I then used 'TestPriceRpt' as the basis for designing the report 'PriceSheet'.  It all works well except that the user doesn't always want every entry in 'TestPriceRpt' on the report 'PriceSheet'.  What I need to know is how to filter the entries on the report in the VB code.

I tried the suggestions that you gave and got the error mentioned.

What is TestPriceRpt. A Sql String or a recordset or a tablename ...

TestPriceRpt was built in the DataEnvironment.  It is a 'command' off of a connection.  When building the command I specified the following SQL string in the properties window 'General' tab:

SELECT tblClient.ClientName, tblClientItemHdr.ClientID, tblClientItemHdr.ClientItemHdrID, tblClientItemHdr.ItemID, tblClientItemHdr.ItemSeq, tblClientItemDtl.ClientItemDtlSeq, tblClientItemDtl.ItemDesc, tblClientItemDtl.ItemPrice, tblItems.Style, tblItems.Color
FROM ((tblClientItemHdr INNER JOIN tblClientItemDtl ON tblClientItemHdr.ClientItemHdrID = tblClientItemDtl.ClientItemHdrID) INNER JOIN tblClient ON tblClientItemHdr.ClientID = tblClient.ClientID) INNER JOIN tblItems ON tblClientItemHdr.ItemID = tblItems.ItemID;

I renamed the command 'TestPriceRpt'

Once I established this command, I used it as the basis for developing a report 'rptDE_PriceSheet' in the VB datreport designed.  The report of course prints everything in the file, which is not always what I want.  I am asking for a way to apply a filter to either the report or command 'TestPriceRpt' at runtime so the report prints only the items selected by the user.