Print Page | Close Window

A day number must be between 1 and the...... error

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=18979
Printed Date: 02 May 2024 at 5:06am


Topic: A day number must be between 1 and the...... error
Posted By: Shales
Subject: A day number must be between 1 and the...... error
Date Posted: 30 Jan 2013 at 2:25am
Hello,
 
I am really struggling to write a report and I am looking for some help.
 
First of all I am using this formula which is the major driving formula in my report
 
IIF(DATE(YEAR(TODAY()),MONTH( mailto:%7b@Date - {@Date Pol COm}),DAY( mailto:%7b@Date - {@Date Pol COm})) < TODAY(),DATE(YEAR(TODAY())+1,MONTH( mailto:%7b@Date - {@Date Pol COm}),DAY( mailto:%7b@Date - {@Date Pol COm})),DATE(YEAR(TODAY()),MONTH( mailto:%7b@Date - {@Date Pol COm}),DAY( mailto:%7b@Date - {@Date Pol COm})))
 
What this is doing is taking the clients start date and then seeing if the anniversary has passed this year or is due this year. If the anniversary has passed it displays the date 29/01/2014 as today is 30/01/2013.
 
This works ok which is great.
 
The problem is, I am looking for all plans that have yesterday's date. If I try any selection criteria using this formula field it displays the following message;
 
"A day number must be between 1 and the number of days in the month"
 
I can't even sort the report using this column. I am confused. Please can anybody help. Maybe there is a better way of writing my formula?
 
Regards
Gavin
 
 


Replies:
Posted By: DBlank
Date Posted: 30 Jan 2013 at 4:32am
what is your @Date Pol Com formula?

Posted By: Shales
Date Posted: 30 Jan 2013 at 4:37am
It's just;
 
date({POLICYINFORMATIONVW.POLICYCOMMDATE})
 
I was just trying to convert the field first before using it in the formula to see if that worked.
 
The formula doesn't work with just ({POLICYINFORMATIONVW.POLICYCOMMDATE}) either. This field is DATETIME, but the time is always 00:00:00
 
Don't know if that helps?
 
Thanks for looking

Posted By: DBlank
Date Posted: 30 Jan 2013 at 4:39am
so you want yesterdays date if the anniversary is passed for the year and the actual anniversary date if it has not correct?

Posted By: Shales
Date Posted: 30 Jan 2013 at 4:41am
Also, I am now using the above formula without the "+1" as I am looking for the formula to just return current YYYY. It would have been impossible to find yestersdays date otherwise.

Posted By: Shales
Date Posted: 30 Jan 2013 at 4:44am
Sorry, we replied at the same time.
 
Basically I am now just looking at the ({POLICYINFORMATIONVW.POLICYCOMMDATE}) which could be 2007, 2008 etc, and I want them all to be current YYYY.
 
That way I can find the plans that had their anniversary yesterday - (today -1)

Posted By: DBlank
Date Posted: 30 Jan 2013 at 4:48am
how are you handling leap years?

Posted By: Shales
Date Posted: 30 Jan 2013 at 4:58am
er.... not sure. I am hoping that a leap year wouldn't impact the formula.
 
I haven't even considered leap years. I have had a look at my data and there are no plans that commenced on 29/02/2012 or 29/02/2008

Posted By: DBlank
Date Posted: 30 Jan 2013 at 5:33am
 
one way to convert to current year:
dateadd('yyyy',datediff('yyyy',{POLICYINFORMATIONVW.POLICYCOMMDATE},currentdate),{POLICYINFORMATIONVW.POLICYCOMMDATE})
 
one way to find any for yesterday:
datediff('d',dateadd('yyyy',datediff('yyyy',{POLICYINFORMATIONVW.POLICYCOMMDATE},currentdate),{POLICYINFORMATIONVW.POLICYCOMMDATE}),currentdate)=1

Posted By: Shales
Date Posted: 30 Jan 2013 at 5:40am
Thanks v much for the formulas. I shall try this first thing in the morning.
 
Regards
Gavin

Posted By: Shales
Date Posted: 30 Jan 2013 at 11:02pm
Both formulas are wonderful. Thanks DBlank : )
 
Regards
Gavin


Print Page | Close Window