Print Page | Close Window

find customer by birthday

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=13466
Printed Date: 28 Apr 2024 at 2:49pm


Topic: find customer by birthday
Posted By: trba
Subject: find customer by birthday
Date 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


Replies:
Posted By: Keikoku
Date 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.

Posted By: trba
Date 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

Posted By: CircleD
Date 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.

Posted By: trba
Date 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


Print Page | Close Window