filter data between 2 dates in crystal report vb.net

Question

I need some help for creating Crystal Reports in VB 2005.

I want to filter data between two dates (like from date and to date) with datetimepicker. I'm using SQL Server 2000 for the connection.

Update:

Thanks for the link, but I'm trying using record selection formula....

Here's my code:

Try
        Dim cryRpt As New ReportDocument
        With cryRpt
            .FileName = ("C:\Documents and Settings\Ratna Ayu\My Documents\Visual Studio 2005\Projects\Denda\Denda\CrystalReport1.rpt")
            .RecordSelectionFormula = "{pinjam.tglkembali}>='" & DateTimePicker1.Value.ToString("dd/MM/yyyy") & "' and {pinjam.tglkembali} =<'" & DateTimePicker2.Value.ToString("dd/MM/yyyy") & "'"

        End With
        CrystalReportViewer1.ReportSource = cryRpt
        CrystalReportViewer1.Refresh()
    Catch ex As Exception
        MsgBox("tdk ada data", , "")
    End Try

Answer 1

Have a look at this link

How to Load and Display Crystal Reports in VB.NET

The following code shows how to load Crystal Reports in VB.NET, solving all the issues of logon, including sub reports and parameter passing. You can view your reports by simply calling the required functions with its parameters.

Answer 2

Public Sub ConfigureCrystalReportsPhotoAlbum2012ByDateNew()
        On Error Resume Next
        sReport = New ReportDocument()
        Dim reportPath As String = Application.StartupPath & "\" & "PhotoAlbum2012.rpt"
        sReport.Load(reportPath)
        Dim selectionFormula As String = "{MemberDetails.Birthdate} >= #" _
            & CDate(frmCustomReport.txtFrom.Text) _
            & "# and {MemberDetails.Birthdate}" _
            & "<=" _
            & "#" _
            & CDate(frmCustomReport.txtTo.Text) _
            & "#"
        Dim myConnectionInfo As ConnectionInfo = New ConnectionInfo()
        'myConnectionInfo.ServerName = "192.168.0.2\SQLExpress" 'Network
        myConnectionInfo.ServerName = ".\SQLExpress"   'Local
        myConnectionInfo.DatabaseName = "Photo Album 2012"

        myConnectionInfo.UserID = "limitedPermissionAccount"
        myConnectionInfo.Password = ""

        myConnectionInfo.IntegratedSecurity = True
        SetDBLogonForReport(myConnectionInfo, sReport)
        frmMain.CrystalReportViewerMain.SelectionFormula = selectionFormula 

        frmMain.CrystalReportViewerMain.ReportSource = sReport
        frmMain.CrystalReportViewerMain.Zoom(100)
        frmMain.CrystalReportViewerMain.BackColor = Color.AliceBlue
    End Sub

Answer 3

That's going to far just use this code its very easy. I built a sample contact application and a report for it and used the code below to filter contacts according to start and end date. DateTimePicker1 is start and DateTimePicker2 is end.

Private Sub filter_dates()
    Dim objcr As New CrystalReport1
    Dim da As New MySqlDataAdapter("select * from Contacts where CDate>='" & CDate(DateTimePicker1.Text) & "' and CDate<='" & CDate(DateTimePicker2.Text) & "' ", conn)
    Dim dt As New DataTable
    da.Fill(dt)
    objcr.SetDataSource(dt)
    CrystalReportViewer1.ReportSource = objcr
    CrystalReportViewer1.RefreshReport()
End Sub