Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: find customer by birthday Post Reply Post New Topic
Author Message
trba
Newbie
Newbie


Joined: 15 Jan 2009
Online Status: Offline
Posts: 7
Quote trba Replybullet 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

where ?days is entered on running the report.

hope this makes sense

thanks in advance






Edited by trba - 13 Jun 2011 at 5:44am
IP IP Logged
Keikoku
Senior Member
Senior Member


Joined: 01 Dec 2010
Online Status: Offline
Posts: 386
Quote Keikoku Replybullet 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)

Not sure how the check would be done.

Edited by Keikoku - 10 Jun 2011 at 7:10am
IP IP Logged
trba
Newbie
Newbie


Joined: 15 Jan 2009
Online Status: Offline
Posts: 7
Quote trba Replybullet Posted: 12 Jun 2011 at 11:50pm
thanks,

ive tried just using the Date(year(CurrentDate),month({STATS.DATEOFBIRTH}), day({STATS.DATEOFBIRTH}))

but it comes back saying a day number must be between 1 and the number of days in the month

not sure how to get around this
IP IP Logged
CircleD
Senior Member
Senior Member
Avatar

Joined: 11 Mar 2011
Location: United States
Online Status: Offline
Posts: 251
Quote CircleD Replybullet 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.
IP IP Logged
trba
Newbie
Newbie


Joined: 15 Jan 2009
Online Status: Offline
Posts: 7
Quote trba Replybullet Posted: 13 Jun 2011 at 11:25pm
hi there,

i tried various versions of that coed and couldn't get it to work i finally tried the following which worked.

if month(CurrentDate) * 100+day(CurrentDate) > Month({STAT_CARDS.CARDHOLDERDATEOFBIRTH}) * 100 + day ({STAT_CARDS.CARDHOLDERDATEOFBIRTH})
then Date( Year (CurrentDate) + 1, Month({STAT_CARDS.CARDHOLDERDATEOFBIRTH}), Day({STAT_CARDS.CARDHOLDERDATEOFBIRTH}) )
else Date( Year (CurrentDate)    , Month({STAT_CARDS.CARDHOLDERDATEOFBIRTH}), Day({STAT_CARDS.CARDHOLDERDATEOFBIRTH}) )


thanks for your help
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
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



This page was generated in 0.016 seconds.