I am using Crystal XI professional and trying to compare a date that is kept in the database as a number (not the typical so many days from 1899). It is actually the date formatted but stored as a number (e.g., 20090731). I need to reference the nubmer to limit the amount of records I bring back and optomize the report.
I convert my date in the formuas as follows:
// Convert Date - convert date to text to compare against date stored as number in DB
stringvar D:= IIF(length(totext(day(currentdate-1)))=1,"0"& totext(day(currentdate-1)),totext(day(currentdate-1)));
stringvar M:= IIF(length(totext(month(currentdate-1)))=1,"0"& totext(month(currentdate-1)),totext (month(currentdate-1)));
totext(year(currentdate-1))+M+D
and then in the record section I compare this to the date by changing it to a number for the match.
It works perfectly as designed when I run the report on my workstaiton with my Crystal report writer application.
The problem is when I schedule it in our Crystal Enterpris XI (Business Objects 11.5) it errors out with the follow error and appears that the standard "tonumber" function does not work or does not work the same in the enterprise version.
The error I get is.
Error in File D:\Program Files\Business Objects\BusinessObjects Enterprise 11.5\Data\procSched\hhcrystal2.reportjobserver\~tmp210c5c882de05345.rpt: Error in formula <Record Selection>. 'tonumber({@DateNumConversion})={History.XDate} ' The string is non-numeric. Details: errorKind
I don't understand why both Crystal report version do not work the same. I have tried approaching it by creating another forumua in the report to convert the text date to a number and then referencing the variable but it give the same error when it executes the variable that it gives in the record selection.
Any suggestions?