Joined: 15 Jan 2009
Online Status: Offline
Posts: 7
Topic: find customer by birthday Posted: 10 Jun 2011 at 4:09am
Hi Guys,
im trying to create a report that shows customers who's birthday is coming up in the next x days.
im trying to use the following formula field to generate the birthday but it comes up saying that everything after the -1 does not appear to be part of the formula.
this formula is supposed to handle the leap year affect in February when run in non leap years. also im unsure of how to make it so that when it comes to running the report in December for the next 2 weeks into January it picks up the next year or would this not matter as i am only interest in their birthday and not age?
If month({STATS.DATEOFBIRTH}) = 2 and day({STATS.DATEOFBIRTH})=29 Then Date(Year(CurrentDate),3,1) –1 else Else Date(year(CurrentDate),month({STATS.DATEOFBIRTH}), day({STATS.DATEOFBIRTH})
i then use this formula field in the selection criteria to show the customers:
{@birthday} in currentdate to currentdate + {?Days} and
Joined: 01 Dec 2010
Online Status: Offline
Posts: 386
Posted: 10 Jun 2011 at 7:08am
Crystal's date functions handle leap years correctly (according to the help file).
So if you're just checking the next 2 weeks, use dateadd('d', 14', currentDate()) or something of that sort to add 2 weeks to the current date and then check whether the customer's birthdate is within the range (currentDate() <= now <= 2 weeks from now)
Joined: 11 Mar 2011
Location: United States
Online Status: Offline
Posts: 251
Posted: 13 Jun 2011 at 11:58am
Originally posted by trba
If month({STATS.DATEOFBIRTH}) = 2 and day({STATS.DATEOFBIRTH})=29 Then Date(Year(CurrentDate),3,1) –1 else Else Date(year(CurrentDate),month({STATS.DATEOFBIRTH}), day({STATS.DATEOFBIRTH})
Your error is most likely you have Else twice together in the formula.Remove one and recheck the syntax.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum