Adding SqlCommand date range parameters to Crystal Report

Question

I am creating a VB.NET project that involves Crystal Reports. I create my reports using the following SQL command:

If (limitByDate) Then
        dateClause = " (([Submit Time]>= '" & DateTimeToSqlTime(beginDate) & "') And ([Submit Time]<= '" & DateTimeToSqlTime(endDate) & "'))"
End If

sqlAdapter.SelectCommand = New SqlCommand("SELECT * FROM [Submission Transactions] WHERE " & dateClause & " ORDER BY [Submit Time] DESC", ServerDB.getConn)

I need to display the beginDate and endDate on the Crystal Report. I do not want to use Crystal Reports built in date range selector. These parameters are not shown in the Crystal Report dialogue and there is not necessarily an entry on the beginDate or endDate. I.e. I cannot just do a minimum and maximum of current entries.

What are some options to display these dates on my report?

I am using .NET 4.6. Can use either VB or C#. I am not positive of Crystal Reports version, but use the plugin for Visual Studio 2015.

Transactions are created by our customer as they operate our system. So they could have transactions on 10/28 10/29. However they may request a report from 10/27 thru 10/30. I want those two dates shown even if there are only transactions on 10/28 and 10/29. — J.Keeling

J.Keeling J.Keeling · Accepted Answer · 2015-11-02T14:25:43

This can be solved by creating an empty text object and setting its value in vb.net code:

 Dim FObj As CrystalDecisions.CrystalReports.Engine.TextObject = rpt.ReportDefinition.ReportObjects("Text10")
        FObj.Text = beginDate.ToString

This will fill the text object labeled "Text10" with the string version of the beginDate. Repeat for endDate.

Adding SqlCommand date range parameters to Crystal Report

1 Answers