on 06-29-2016 1:12 PM
Hie guys
I have the below formula that should pull the day in the next month. When I run crystal reports it gives the error "A day number must be between 1 and the number of days in a month crystal reports error" I assume that is because 31 does not exist in the current or next month, for instance there is no 31 June. How do I modify the below formula to consider the days in a month.
IF ISNULL({CASE_EXTRA.FIELD055_DATE}) OR {CASE_EXTRA.FIELD055_DATE}=DATE(0,0,0) THEN
IF PrintDate > DATE(DatePart ("yyyy",PrintDate ),DatePart ("m",PrintDate ),{DEBITORDERARRANGEMENT.DAYOFMONTH}) THEN
DATE(DatePart ("yyyy",PrintDate ),(DatePart ("m",PrintDate ) + 1),{DEBITORDERARRANGEMENT.DAYOFMONTH})
ELSE
DATE(DatePart ("yyyy",PrintDate ),DatePart ("m",PrintDate ),{DEBITORDERARRANGEMENT.DAYOFMONTH})
ELSE
IF PrintDate > {CASE_EXTRA.FIELD055_DATE} THEN
DATE(DatePart ("yyyy",{CASE_EXTRA.FIELD055_DATE} ),(DatePart ("m",{CASE_EXTRA.FIELD055_DATE} ) + 1),DatePart ("d",{CASE_EXTRA.FIELD055_DATE} ))
ELSE
{CASE_EXTRA.FIELD055_DATE}
;
Hi Star,
I think there is some problem with this filed : {DEBITORDERARRANGEMENT.DAYOFMONTH}, this field contains a number which is not falling in 1 to 31. or it could be null
Thanks,
Sastry
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Star,
If you are manipulating date, then it should never be NULL or contains Alphabets. This will give an error while manipulating date part.
Please try to use below logic when you add or reduce months or years.
DATE(year(Dateadd('m',+2,printdate)),Month(Dateadd('m',+2,printdate)),01)-1
If you look at your formula for adding a month :
DATE(DatePart ("yyyy",PrintDate ),(DatePart ("m",PrintDate ) + 1),{DEBITORDERARRANGEMENT.DAYOFMONTH})
suppose if you are trying to add one month for 12/31/2016, then the above formula will add one month for month part then it will be 13 not 01 and also year will be of 2016 only it will not be 2017. so, always use DateAdd() when you are adding any value to date.
See below formula, I have replaced '{DEBITORDERARRANGEMENT.DAYOFMONTH}' with Printdate.
IF ISNULL({CASE_EXTRA.FIELD055_DATE}) OR {CASE_EXTRA.FIELD055_DATE}=DATE(0,0,0) THEN
IF PrintDate > DATE(DatePart ("yyyy",PrintDate ),DatePart ("m",PrintDate ),day(currentdate)) THEN
DATE(year(Dateadd('m',+2,printdate)),Month(Dateadd('m',+2,printdate)),01)-1
ELSE
DATE(DatePart ("yyyy",PrintDate ),DatePart ("m",PrintDate ),day(currentdate))
ELSE
IF PrintDate > {CASE_EXTRA.FIELD055_DATE} THEN
DATE(DatePart ("yyyy",{CASE_EXTRA.FIELD055_DATE} ),(DatePart ("m",{CASE_EXTRA.FIELD055_DATE} ) + 1),DatePart ("d",{CASE_EXTRA.FIELD055_DATE} ))
ELSE
{CASE_EXTRA.FIELD055_DATE}
Thanks,
Sastry
User | Count |
---|---|
80 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.