Dates must be between year 1 and year 9999 error message

[RCCRookie]

Crystal XI version 11.0.0.1282
SQL Server 2000
MS Windows XP Professional Version 2002 Service Pack 3

I am getting the "Dates must be between year 1 and year 9999" error message when I run my report. The following formula comes up and the bolded line is the line highlighted in the Crystal formula editor. I don't have the slightest idea how to modify this formula to make it work. I'd appreciate any help I can get.

EvaluateAfter({@Year});
EvaluateAfter({@Month});

Global DateVar StartDate;

Global Stringvar strMonth;
Global StringVar strYear;

Global NumberVar nSuppressYear:=CDbl(strYear);
Global NumberVar nSuppressMonth:=CDbl(strMonth);
Global NumberVar nSuppressNextMonth:=nSuppressMonth+1;

Global StringVar strSuppressEndDay:=ToText(Day(DateSerial(nSuppressYear,nSuppressNextMonth,1-1)),0);

 

[CoSpringsGuy]

I assume the values of strYear and strMonth came from another formula?

Have you considered null values for those variables?




_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[RCCRookie]

Month formula:
Global Stringvar strMonth:=Totext(Month({DOCUMENT_MNT_V.NEXT_DATE}),0)

Year formula:
Global StringVar strYear:=ToText(Year({DOCUMENT_MNT_V.NEXT_DATE}),0,"")

Sorry I'm troubleshooting a report but have very little knowledge of formulas. I'm just now beginning to learn about variables and this formula is way beyond my 'scope'.
I have provided the Year and Month formula which is where I believe the strMonth and strYear are declared. I hope this helps. Oh and no I haven't considered using a null value for those variables. As I mentioned earlier I'm inexperienced in this area.

 

[CoSpringsGuy]

I would have structured your declarations different but see if this fixes the issue. If you want some suggestions for your formula to flow a little better post back and Im sure someone will jump on that and provide some opinion.

in your original formula that you posted replace the last line with this:

if isnull({@Year}) = false then Global StringVar strSuppressEndDay:=ToText(Day(DateSerial(nSuppressYear,nSuppressNextMonth,1-1)),0)
else Global StringVar strSuppressEndDay:=""

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[RCCRookie]

Still getting the same error message and the same line is highlighted (see bolded text)

EvaluateAfter({@Year});
EvaluateAfter({@Month});

Global DateVar StartDate;

Global Stringvar strMonth;
Global StringVar strYear;

Global NumberVar nSuppressYear:=CDbl(strYear);
Global NumberVar nSuppressMonth:=CDbl(strMonth);
Global NumberVar nSuppressNextMonth:=nSuppressMonth+1;

//Global StringVar strSuppressEndDay:=ToText(Day(DateSerial(nSuppressYear,nSuppressNextMonth,1-1)),0);

if isnull({@Year}) = false then Global StringVar strSuppressEndDay:=ToText(Day(DateSerial(nSuppressYear,nSuppressNextMonth,1-1)),0)
else Global StringVar strSuppressEndDay:=""

 

[CoSpringsGuy]

go back to your original entry....

EvaluateAfter({@Year});
EvaluateAfter({@Month});

Global DateVar StartDate;

Global Stringvar strMonth;
Global StringVar strYear;

Global NumberVar nSuppressYear:=CDbl(strYear);
Global NumberVar nSuppressMonth:=CDbl(strMonth);
Global NumberVar nSuppressNextMonth:=nSuppressMonth+1;

//Global StringVar strSuppressEndDay:=ToText(Day(DateSerial(nSuppressYear,nSuppressNextMonth,1-1)),0);

nSuppressYear

put that in your details and see what it is returning. The error message indicates a bad year value. Dong the above will show you whats being plugged into the dateserial formula.



_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

As an aside, this seems to be overly complicated. First you are converting dates to text and back again, for no clear reason. And so far I haven't seen anything that would even indicate the need for variables. What is this formula intended to accomplish? Wny not just use:

day(dateserial(year({DOCUMENT_MNT_V.NEXT_DATE}), month({DOCUMENT_MNT_V.NEXT_DATE})+1, 1)-1)

This would return the last day number of the month containing the next_date field.

-LB

 

[CoSpringsGuy]

Good point ... I have to remember to look back at why someone is doing something rather than try to fix the formula they present I spose.....

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[RCCRookie]

CoSpringsGuy - I tried your suggestion but still get the same error message. I don't get any data returned at all, just the error message.

Lbass -it's a calendar report with a bunch of subreports that was written way before I came along. If it doesn't make sense to you I feel better about the fact that it's gibberish to me. I have very little experience in the formula and data manipulation area. I can handle simple basic formulas and that's about my limit at this stage. Although I'm beginning to learn about variables and data manipulation I have a long road to hoe. We have a customer that is getting this error message and I need to fix the problem. I have a feeling that the customer may have some weird date data but I'm not sure how that could be possible. Hope this helps you understand the wacky formula I posted.

 

[lbass]

If you are using subreports, is it possible that the variables need to be shared between the sub and main reports? If so, you need to set them up as shared variables, not global.

-LB

 

[RCCRookie]

Thanks for the responses lbass and CoSpringsGuy. My eyes are crossing at the idea of delving into the formulas in this report. I'm definitely in wayyyyy over my head on this report. I'll have to give your ideas a try and hope that both I and the report survive to tell the tale. Thanks to both of you for your help. If I manage to get this thing working properly I'll post back with my workaround.