CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2006
    Location
    Kolkata, India
    Posts
    278

    Unhappy Crystal Report XI R2 Problem

    Dear Everyone,

    I have designed the Report manually & in a Command button on a form
    I have written
    Code:
    
    
    Code:
    Dim sStartDate As Date
    Dim sEndDate As Date
    Dim sSQL As String
    
    
    sStartDate = Format(DTPicker1.Value, "dd/mm/yyyy")
    sEndDate = Format(DTPicker2.Value, "dd/mm/yyyy")
    
    
    sSQL = "select * from Sales where SellDate BETWEEN " & sStartDate & " and " & sEndDate
    DataEnvironment1.Partsale sStartDate, sEndDate
    frmPSale.Show
    The Report returns all Records Beyond the dates I entered !!!
    I am using CrystalReports XI R2
    Is there anyone who can help me with this?please...
    Pls help

    Rahul
    Last edited by dsrahul; November 11th, 2013 at 05:27 AM. Reason: No helps !!!

  2. #2
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,283

    Re: Crystal Report XI R2 Problem

    Sometimes you have to include # around your dates

  3. #3
    Join Date
    Apr 2006
    Location
    Kolkata, India
    Posts
    278

    Cool Re: Crystal Report XI R2 Problem

    Quote Originally Posted by HanneSThEGreaT View Post
    Sometimes you have to include # around your dates
    tried those too & with the SQL query also # and #
    # with dates , but same story, I am getting fed up.

  4. #4
    Join Date
    Jun 2005
    Location
    JHB South Africa
    Posts
    3,772

    Re: Crystal Report XI R2 Problem

    Problem with dates is that each system can interpret them differently, and there is no single place to set the date format system wide...

    some areas use dd/mm/yyyy and there are some that use mm/dd/yyyy ... now if we look at this date... 1/12/2013, it's either 1st December 2013, or 12th January 2013... Very very different dates..

    Now unless otherwize setup SQL will like dates to be yyyy/mm/dd ... (erm now thats different to what i was listing before).. and as such uses a date decoder to figure out the date its been passed.. now You can do one of several things..

    1) Always format the date the way SQL wants it...
    2) Use Stored procedures to pass dates (the method VB passes Dates to Stored procedures ensures they get there correctly)
    3) use the LONG date format. Ie .. instead of passing #1/12/2013# pass #1st December 2013#
    Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
    WPF Articles : 3D Animation 1 , 2 , 3
    Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
    Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
    All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.

  5. #5
    Join Date
    Apr 2006
    Location
    Kolkata, India
    Posts
    278

    Re: Crystal Report XI R2 Problem

    Dear Gremlin,

    A little more elaboration, it feels like the movie ended all of a sudden

    Rahul

  6. #6
    Join Date
    Jun 2005
    Location
    JHB South Africa
    Posts
    3,772

    Re: Crystal Report XI R2 Problem

    Okay here's a simple DEMO..

    Open Excel.... In the first column format the cells to display date in a long format (I use a custom type)...
    Name:  ExcelCellFormat.jpg
Views: 1181
Size:  73.6 KB

    then copy paste this list of differently formated (numerical) dates and see which ones Excel can properly reformat. (you are looking for the 10th to 14th January 2013)...
    Code:
    01/10/2013
    01/11/2013
    01/12/2013
    01/13/2013
    01/14/2013
    10/01/2013
    11/01/2013
    12/01/2013
    13/01/2013
    14/01/2013
    2013/01/10
    2013/01/11
    2013/01/12
    2013/01/13
    2013/01/14
    2013/10/01
    2013/11/01
    2013/12/01
    2013/13/01
    2013/14/01
    According to region settings different ones will be understood, to varying degrees of success..

    The best method to pass dates between applications is to use the full datetime setup. IE... 10 January 2013 like described in Example 3 in my previous post..

    Next is by using Stored procedures, the date is acctually passed as a full numeric.. IE.. the list below is the numeric equivalents of the dates listed above
    Code:
    41284
    41285
    41286
    41287
    41288
    Copy and paste these into the same excel column as before, and there you have the dates without any hassel of parsing dates...
    Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
    WPF Articles : 3D Animation 1 , 2 , 3
    Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
    Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
    All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.

  7. #7
    Join Date
    Apr 2006
    Location
    Kolkata, India
    Posts
    278

    Re: Crystal Report XI R2 Problem

    Gremlin

    It is Access, not Excel !!!

  8. #8
    Join Date
    Jun 2005
    Location
    JHB South Africa
    Posts
    3,772

    Re: Crystal Report XI R2 Problem

    Quote Originally Posted by dsrahul View Post
    Gremlin

    It is Access, not Excel !!!
    Dah ...

    I'm showing you a DEMO or EXAMPLE in EXCEL.. where you CAN SEE how the date formats work...

    Try it..... IN EXCEL...
    Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
    WPF Articles : 3D Animation 1 , 2 , 3
    Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
    Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
    All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.

  9. #9
    Join Date
    Jul 2005
    Posts
    1,083

    Re: Crystal Report XI R2 Problem

    I don't see how you are passing the selected recordset to the report... post the code...
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

  10. #10
    Join Date
    Apr 2006
    Location
    Kolkata, India
    Posts
    278

    Re: Crystal Report XI R2 Problem

    Code:
    DataEnvironment1.Partsale sStartDate, sEndDate
    That way I am passing it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured