Data Formula Help

[R00k1e]

Hello,

I'm currently using Crystal Reports 9 and need helping writing a formula.
I have a parameter created with a value type of 'Date'. I need to create a formula that will give me the dates between whatever date is chosen from the parameter through June 30th. For instance, if I chose a date of 10-4-11, I want to find out what days are between 10/4/11 - 6/30/12. If I pick todays date, it would give me the days between 4/26/11 - 6/30/11. I hope this makes sense. Any help would be greatly appreciated.

Kind Regards

 

[Turkbear]

Hi,
If the field in the database is an actual Date type then use:

{Table.Date} In {?ParameterDate } to Date(2012,06,30)






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Madawc]

If you want whole months after the current date, using DateDiff. Something like

{data.date} > @paramdate
and DateDiff("m", @paramdate, {data.date}) <= 2

I am assuming you specify values like '4/26/11' as a parameter.

You could also parameterise the number of months to look forward.


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[R00k1e]

Thank you for the feedback! I guess my issue is that I cannot hard code the end date. I need it so that when choose any date in any year, it will give me that date to the following June 30th.

 

[IanWaterman]

Create formula

@EndDate

If month(paramDate) >= 6 then
Date(year(paramdate)+1, 6, 30)
else
Date(year(paramdate), 6, 30)


Ian

 

[lbass]

Tweak: Ian's enddate formula should start with:

If month(paramDate) > 6 then

-LB

 

[R00k1e]

Hi guys,
I created my formula similar to what was previously suggested, but I am getting a formula error. It is saying I have too many arguments and the '6,30' was highlighted. Any suggestions?


If month({paramDate})> 6 then Date(year({paramDate}+1, 6, 30)) else Date(year({paramDate}, 6, 30))

 

[Turkbear]

Hi,
Maybe the parens:

If month({paramDate})> 6 
then 
Date(year({paramDate}+1[COLOR=red])[/color], 6, 30) 
else 
Date(year({paramDate}[COLOR=red])[/color], 6, 30)

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[IanWaterman]

It was your parens on both Year functions, should be

If month({paramDate})> 6 then Date(year({paramDate})+1, 6, 30) else Date(year({paramDate}), 6, 30)

PS Turkbear's suggestion would have only added a day to the date Param.

Ian

 

[R00k1e]

Thank you everyone for the help. Works perfect now.

 

[Turkbear]

Hi,
Good catch Ian, I misplaced it even though I was commenting on misplaced parens



To Paraphrase:"The Help you get is proportional to the Help you give.."