How to Filter Details Shown On MS Report Using Date

[PRAISE PHS]

Hi All,
Pls I have a windows app called expenses. On this app, I have a text box, date time picker and a combo box . I used the text box for entering expenses details and date time picker for entering date. I then created a Microsoft report for displaying and printing of my records in my sql server database. My challenge is that, how do I code my app such that I can use dates( like start date - end date) to filter what are being displayed on my report. Thanks in anticipation of your responses.

 

[jmcilhinney]

What's the data source for the report? Is it a DataTable? If so then you are populating that using a query against your database, so you simply add a WHERE clause as you usually would to filter data.

Alternatively, you may be able to design the filters into the report itself instead of using external controls. That's what I've always done, but I've only ever used server reports so I'm not 100% sure whether client reports have the same support.

 

[PRAISE PHS]

What's the data source for the report? Is it a DataTable? If so then you are populating that using a query against your database, so you simply add a WHERE clause as you usually would to filter data.

Alternatively, you may be able to design the filters into the report itself instead of using external controls. That's what I've always done, but I've only ever used server reports so I'm not 100% sure whether client reports have the same support.

Thanks Jim for the reply. But I have another issue. I put two date time pickers on my app. Named the 1st date control as "from" and the 2nd as "to". I do this in order for me to be able to select the dates on both controls. Now, what kind of query do I use to filter what is been displayed on my report as a result of what I have on the two date time pickers control.

 

[jmcilhinney]

You simply use a WHERE clause with your two dates inserted as values. Are you saying that you don't know how to implement a WHERE clause?

 

[PRAISE PHS]

You simply use a WHERE clause with your two dates inserted as values. Are you saying that you don't know how to implement a WHERE clause?

Thanks for the reply. But my code below is not actually filtering the report outcome and I do not know where the problem is going from. Pls, kindly help me out.
NB: ClientID is a secondary key foreign key on the BankTransportExp database table. Also I have a combo box loaded with the client name and ID from my clients database table and they are splitted (the array string "arst" represents clientID on the combo box)

Adapter = New SqlDataAdapter("SELECT BankTransportExp.ExpDetails, BankTransportExp.Amt , BankTransportExp.Date FROM BankTransportExp, Clients WHERE Clients.ClientID = '" & Integer.Parse(arst) & "' AND Clients.ClientID = BankTransportExp.ClientID AND BankTransportExp.Date >= '" & (dtpFrom.Text) & "' AND BankTransportExp.Date <= '" & (dtpTo.Text) & "'", MyConn)
           
            scb = New SqlCommandBuilder(Adapter)
            ds = New DataSet
            Adapter.Fill(ds, "BankTransportExp")
            rpt.SetDataSource(ds)
            CrystalReportViewer1.ReportSource = rpt

 

[jmcilhinney]

Thanks for the reply. But my code below is not actually filtering the report outcome and I do not know where the problem is going from. Pls, kindly help me out.
NB: ClientID is a secondary key foreign key on the BankTransportExp database table. Also I have a combo box loaded with the client name and ID from my clients database table and they are splitted (the array string "arst" represents clientID on the combo box)

Adapter = New SqlDataAdapter("SELECT BankTransportExp.ExpDetails, BankTransportExp.Amt , BankTransportExp.Date FROM BankTransportExp, Clients WHERE Clients.ClientID = '" & Integer.Parse(arst) & "' AND Clients.ClientID = BankTransportExp.ClientID AND BankTransportExp.Date >= '" & (dtpFrom.Text) & "' AND BankTransportExp.Date <= '" & (dtpTo.Text) & "'", MyConn)
           
            scb = New SqlCommandBuilder(Adapter)
            ds = New DataSet
            Adapter.Fill(ds, "BankTransportExp")
            rpt.SetDataSource(ds)
            CrystalReportViewer1.ReportSource = rpt

The first thing I would do is use "proper" join syntax, e.g.

SELECT *
FROM Parent INNER JOIN Child
ON Parent.ParentId = Child.ParentId

The second thing I would do is use parameters to insert values into SQL code. To learn why and how, follow the Blog link in my signature below and check out my post on Parameters In ADO.NET. Once you've done that, if you still have issues then post your new code, tell us what the parameter values are and tell us what you see in the report that shouldn't be there.

 

[PRAISE PHS]

The first thing I would do is use "proper" join syntax, e.g.

SELECT *
FROM Parent INNER JOIN Child
ON Parent.ParentId = Child.ParentId

The second thing I would do is use parameters to insert values into SQL code. To learn why and how, follow the Blog link in my signature below and check out my post on Parameters In ADO.NET. Once you've done that, if you still have issues then post your new code, tell us what the parameter values are and tell us what you see in the report that shouldn't be there.

Thanks for the reply. But, I can't see the link you talked about.