Writing Code
 Crystal Reports Forum : Crystal Reports .NET 2003 : Writing Code
Message Icon Topic: Date format in SelectionFormula Post Reply Post New Topic
Page  of 2 Next >>
Author Message
wattsjr
Groupie
Groupie
Avatar

Joined: 25 Jun 2007
Location: United States
Online Status: Offline
Posts: 51
Quote wattsjr Replybullet Topic: Date format in SelectionFormula
    Posted: 07 Aug 2007 at 9:51am
I want to use the DateTimePicker(s) to control the date ranges in my selection criteria, but I'm having trouble getting the right format.
 
The DateTimePicker.Text (or .Value) is giving me the date in "mm/dd/yyyy" format, but my "vCNDBReport.CrystalReportViewer1.SelectionFormula" seems to only retrieve records when I pass it a date in "yyyy,mm,dd" format.
 
What am I doing wrong?
 
My database is Oracle.  I hope doesn't make a difference, but if it does, is there a reasonable way to get (convert to) the expected date format?
 
Any help would be truely appreciated.
 
Regards,
-jrw
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 07 Aug 2007 at 11:01am
Change the selection formula to use the CDate() function. This converts a string to the Date data type.
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
wattsjr
Groupie
Groupie
Avatar

Joined: 25 Jun 2007
Location: United States
Online Status: Offline
Posts: 51
Quote wattsjr Replybullet Posted: 07 Aug 2007 at 3:27pm
Hi Brian,
 
I tried your suggestion in the code shown below and still ended up with an "mm/dd/yyyy" format in my selection string.  Am I using it in the wrong place? I have several parameters to concatenate as part of the selection argument.
 
==============

If cboNamedDateRange.Text = "" Then

gFromDate = CDate(DateTimePicker1.Text)

gToDate = CDate(DateTimePicker2.Text)

gSelDateRange = "{SW_CASE.SWDATECREATED} in Date(" & gFromDate & ") to Date (" & gToDate & ") and "

Else

gSelDateRange = "{SW_CASE.SWDATECREATED} in " & cboNamedDateRange.Text & " and "

End If

Select_String = gSelDateRange

Select_String = Select_String & "{BRX_CONTACT.BRXSECURITY} = 'US' and {SW_CUSTOMER.SWREGION} = '120 '"

vCNDBReport.CrystalReportViewer1.SelectionFormula = Select_String

=======
Thanks and Regards,
-jrw
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 07 Aug 2007 at 3:38pm
Why aren't you using Date() for the cboName... dropdown?. Plus, I think you need to put quotes around the data string within the actual selection formula. You have to act like you were typing the data formula into CR manually. Thus, if you would put quotes around a date string when typing it in then you would do the same when building the selection string with code.
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
wattsjr
Groupie
Groupie
Avatar

Joined: 25 Jun 2007
Location: United States
Online Status: Offline
Posts: 51
Quote wattsjr Replybullet Posted: 08 Aug 2007 at 9:14am
The cboNamedDateRange contains a "Collection" of Crystal Date Ranges and is used exclusive of the FromDate and ToDate parameters. The values are:
LastFullMonth
LastFullWeek
MonthToDate
YearToDate
LastYearYTD
Calendar1stQtr
Calendar2ndQtr... etc.
and is used in the selection formula as shown here:
gSelDateRange = "{SW_CASE.SWDATECREATED} in YearToDate and "... plus Security and Region Parms
------
When I was initially testing the Report I used a hard-coded selection string:
gSelDateRange = "{SW_CASE.SWDATECREATED} in Date(2006,01,01) to Date (2007,08,07) and "... plus Security and Region Parms
and it worked as coded.
My final solution was to change the Definition of the global date fields (gFromDate, gToDate) from type "Date" to "String" and use the following to create the desired date format:
 
gFromDate = Format(DateTimePicker1.Value, "yyyy,MM,dd")
gToDate = Format(DateTimePicker2.Value, "yyyy,MM,dd")
 
Since the two options are mutually exclusive (controlled by Radial buttons) the user is kept from entering in bad dates. They either selecta Named Date Range from the dropdown list or the DateTimePicker prohibits bad date entries. And having to edit for bad dates is kept to a minimum.
-jrw
IP IP Logged
mcaavijit
Newbie
Newbie


Joined: 09 Nov 2007
Online Status: Offline
Posts: 1
Quote mcaavijit Replybullet Posted: 09 Nov 2007 at 12:40pm
use the following code


DateTimePicker1.Format = DateTimePickerFormat.Custom
DateTimePicker1.CustomFormat = "yyyy,mm,dd"


now u can use the     DateTimePicker1.value

thats it
IP IP Logged
shabbi
Newbie
Newbie
Avatar

Joined: 21 Oct 2008
Location: India
Online Status: Offline
Posts: 4
Quote shabbi Replybullet Posted: 21 Oct 2008 at 12:23am
hi all,
am facing the problem in crystal report design i selected date time from special fields but the problem is when i view second , third...... n th page the time is not constant seconds are changing i want it to be constant ie report generated time.
Please help.
thanks,
Shabarish.V
IP IP Logged
GlennC
Newbie
Newbie
Avatar

Joined: 24 Oct 2008
Location: United States
Online Status: Offline
Posts: 5
Quote GlennC Replybullet Posted: 24 Oct 2008 at 8:10am
WattsJr,
Just recently encountered and overcome a similar problem. 
 
Here's the heart of the problem:  In older reports, there was an option to "Convert Dates To String".  (Under Report Options in the designer).  For some reports I don't see that option, so I think it's probably not available in new version due to the problem it causes.
 
If the report has it set to convert dates to string, then you have to put in a string representation of a date in the selection formula.
 
If the report has it set to convert to date, or not set at all, then you have to use the crystal Date() function as BrianB suggested.
 
Now, this brings up another problem: After loading a report, how do you see this setting for that report?
 
In C++ you used to have access to this propery in the document object.  I imaging you could in VB too.  For the life of me, I can't find it in C#.  I think they've retired it along with the setting itself in the designer.  This was a huge problem for me.
 
I found a work around.  You have to interrogate the type of the date field as such:
if (crDoc.Database.Tables["yourTable"].Fields["yourdateField"].ValueType == CrystalDecisions.Shared.FieldValueType.StringField)
{ ... use the string format ...}
else
{ ... use the date format ...}
 
What a pain this all was.
 
 
 
 
 
Shabbi, have you tried getting the datetime on an earlier pass, such as WhileReadingRecords or BeforeReadingRecords?  Just a suggestion.
IP IP Logged
shabbi
Newbie
Newbie
Avatar

Joined: 21 Oct 2008
Location: India
Online Status: Offline
Posts: 4
Quote shabbi Replybullet Posted: 24 Oct 2008 at 8:49am
thanks WattsJr for the reply,
actually am using JRCHelper.java fileand CrystalReport.jsp in my application to generate crystal reports i haven't tried using whilereadingrecords or BeforeReadingRecords,can u please share what is
whilereadingrecords ,BeforeReadingRecords all about?
i think we can convert date to string using ToText() method in section formula(i have not tried)so if i convert Date to String will my problem be fixed?
IP IP Logged
GlennC
Newbie
Newbie
Avatar

Joined: 24 Oct 2008
Location: United States
Online Status: Offline
Posts: 5
Quote GlennC Replybullet Posted: 24 Oct 2008 at 10:29am
Shabbi,
Your problem seems to be different from WattsJR's.  It should probably be it's own thread.  But anyway,
 
I'm not too familiar with generating a report with java in this way.  There's special fields called PrintDate and PrintTime.  Yes, you can wrap them in ToText (PrintDate) if you want them to appear in the footer.
 
For me, it prints the same time on all pages.
 
When a report is run, it goes through the report in multiple passes.  I'm sure this is in the book.
 
BeforeReadingRecords, and WhileReadingRecords are times when the report engine is processing the report.  They happen before WhilePrintingRecords.
 
See if it'll let you put "BeforeReadingRecords" in the formula on the first line above "ToText(PrintTime)".
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.047 seconds.