Crystal Report XI R2 Problem
Dear Everyone,
I have designed the Report manually & in a Command button on a form
I have written
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
Re: Crystal Report XI R2 Problem
Sometimes you have to include # around your dates
Re: Crystal Report XI R2 Problem
Quote:
Originally Posted by
HanneSThEGreaT
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. :(
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#
Re: Crystal Report XI R2 Problem
Dear Gremlin,
A little more elaboration, it feels like the movie ended all of a sudden :)
Rahul
1 Attachment(s)
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)...
Attachment 32097
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...
Re: Crystal Report XI R2 Problem
Gremlin
It is Access, not Excel !!!
Re: Crystal Report XI R2 Problem
Quote:
Originally Posted by
dsrahul
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...
Re: Crystal Report XI R2 Problem
I don't see how you are passing the selected recordset to the report... post the code...
Re: Crystal Report XI R2 Problem
Code:
DataEnvironment1.Partsale sStartDate, sEndDate
That way I am passing it.