Click here to Skip to main content
12,694,121 members (34,387 online)
Rate this:
Please Sign up or sign in to vote.
See more: VB VB.NET
I have a project using a dataset that I fill dynamically from a DataContext using a SQL query. Everything works as expected except for the Crystal Report.

The report shows all the data perfectly, but the Date (which is my group field) is off by one day (-1) than the actual date stored in the table.

I have been a FoxPro programmer trying to switch to Visual Studio 2008 and I have struggled with Crystal Reports for ever. Everything is simple but there is always SOMETHING amiss.
Posted 2-Jun-12 11:17am
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Andrew Alix 4-Jun-12 20:13pm
Done all of the above already. The sql query I use displays correctly in a datagriview with the proper date. See my comment in Solution 1 and the code in solution 3 that clarifies the problem and the work around I used. This didn't solve the problem. It just worked around it. Thanks for you help though.
Rate this: bad
Please Sign up or sign in to vote.

Solution 4

Andrew, open "Group Expert" window, select your group (schDate) in right panel, then press "Options..." On Common tab select combobox under the "The section will be printed:" to value "for each day."
I hope it will solve your problem.
Andrew Alix 6-Jun-12 13:20pm
Thank you. It worked perfect. That was irritatingly simple. It seems that you have to jump through a lot of simple hoops to get Crystal reports to work the way it should. FoxPro reports were a lot more simplified and versatile.
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Please check SQL Query which will show results.
For example

SELECT name,id,dt_loss
  FROM [trial_Data]
  where dt_loss ='2012-03-01' and dt_loss='2012-03-06'
group by name,id,dt_loss

SELECT  name,id,dt_loss
  FROM [trial_Data]
  where dt_loss between '2012-03-01' and '2012-03-06'
group by name,id,dt_loss

Also Tell me that what u mean date off one day is it shows wrong date(less than actual stoted in db) or total data displayed less than one day less
Andrew Alix 4-Jun-12 20:14pm
Using your example, I group by dt_loss. The date from the database is for ex. #05/28/12#, but displays on the form as #05/27/12#.

Using the same query for a datagriview, it displays the date correctly as #05/28/12#, not #05/27/12# as the form shows.

There is a work around that I have found. See the code in my answer in solution 3.
Thanks for your reply.
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

Private Sub Form1_Load(ByVal sender As System.Object, _
                       ByVal e As System.EventArgs) Handles MyBase.Load

' The following works perfectly. The only problem is that CrystalReportViewer1
'     uses a Group #1 Name (Group on schDate) yet it displays one day less 
'     than what is actually in the database. Ex. #May 28, 2012# displays as
'     #May 27, 2012#
'     Displaying Group #1 Name (schDate) and the schDate field beside one another
'     on the report, I get #May 27, 2012#, and #May 28, 2012# in each field
'     respectively. The solution was simple. Delete the Group #1 Name from the 
'     report and use schDate field in its place. The report still groups by
'     Group #1 Name but doesn't display it.

' Would still like to know why it does this though

        Dim db As TM_SchoolDataContext = New TM_SchoolDataContext
        Dim ds As New ReportDataSet 'the report I created

        Dim t As DataTable = ds.TmsSchedule 'Data table 1
        Dim s As DataTable = ds.School      'Data table 2 
        Dim sc = From tms In db.TmsSchedules _
                 Join sch In db.Schools On sch.SchoolDate Equals tms.schDate _
                 Where tms.schDate >= firstDate And tms.schDate <= lastDate _
                 Select tms.schDate, tms.schHlights, tms.schT1, tms.schT2, _
                        tms.schT3, tms.TMSReview, _
                        sch.SchoolDate, sch.HLights, sch.T1, sch.T2, sch.T3

        Dim r As DataRow
        Dim rs As DataRow

        For Each dr In sc
            r = t.NewRow()    'Start a new DataRow in ds.TmsSchedule
            r("schDate") = dr.schDate
            r("schHLights") = dr.schHlights
            r("schT1") = dr.schT1
            r("schT2") = dr.schT2
            r("schT3") = dr.schT3
            r("TmsReview") = dr.TMSReview

            rs = s.NewRow()
            rs("SchoolDate") = dr.SchoolDate
            rs("HLights") = dr.HLights
            rs("T1") = dr.T1
            rs("T2") = dr.T2
            rs("T3") = dr.T3

        Dim objRpt As New rptSchoolReport
        CrystalReportViewer1.ReportSource = objRpt
    End Sub

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web01 | 2.8.170118.1 | Last Updated 5 Jun 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100