Calculating Customer's 1/2 Birthday (not AGE)

  • Thread starter Thread starter poochbeast
  • Start date Start date
P

poochbeast

I am in the insurance industry. Insurance rates are based on whichever
birthday you're currently closer to. I understand how to calculate a
customer's age based on his birthday, but I need to calculate his
"nearest age change" date. This would be his 1/2 birthday. If the
customer's birthday is within the next 6 mos., then I would want the
formula to return a future date. If the customer's birthday was less
than 6 mos. ago, then I would want the formula to return a date in the
past.

I don't know how to create this formula. Logically, the first part of
it would include his birthdate + 6 mos. +1 day. Is there someone
here smart enough to help me do this. I'm terrible with math. Thanks.

Paul

P.S. - Remember, I'm looking for this formula to return a date, not an
age.
 
I believe this would do the job:

DateAdd("yyyy",Abs(DateValue(Format([dob],"dd/mm/") & Year(Now()))<Now())
,DateValue(Format([dob],"dd/mm/") & Year(Now())))

Not very elegant, I'm sure it could be improved upon, but I will explain it
below and see if others can suggest improvements / other ways of doing this:

Hold on, damn it - i've not answered your question at all, this just
calculates when their next birthday is.

Ok, back to the drawing board..... damn I feel foolish
 
poochbeast said:
I am in the insurance industry. Insurance rates are based on whichever
birthday you're currently closer to. I understand how to calculate a
customer's age based on his birthday, but I need to calculate his
"nearest age change" date. This would be his 1/2 birthday. If the
customer's birthday is within the next 6 mos., then I would want the
formula to return a future date. If the customer's birthday was less
than 6 mos. ago, then I would want the formula to return a date in the
past.

I don't know how to create this formula. Logically, the first part of
it would include his birthdate + 6 mos. +1 day. Is there someone
here smart enough to help me do this. I'm terrible with math. Thanks.

Paul

P.S. - Remember, I'm looking for this formula to return a date, not an
age.
Click to expand...

This will do it:

Function NearestAgeChange(Birth As Date)
Dim LastBDay As Date
Dim BDayThisYear As Date
BDayThisYear = DateSerial(Year(Date), Month(Birth), Day(Birth))
BDayLastYear = DateSerial(Year(Date) - 1, Month(Birth), Day(Birth))
If DateDiff("m", Date, BDayThisYear) <= 6 Then
NearestAgeChange = BDayThisYear
Else
NearestAgeChange = BDayLastYear
End If
End Function

Tom Lake
 
Tom,

This function does not take into account days, only months. The DateDiff
function will return only whole numbers, therefore if somebody's birthday
was on the 31st of March and you compared it with the 25th of March (today)
, it would return 0. This would be fine in this instance, but reverse it
and you see you have a problem - somebody whose birthday is the 10th of
March would still return 0, and therefore return their birthday this year.

I think.... i've been thinking on this one too long and have had too much
of that bottle of wine to do any thinking really
 
John Webb via AccessMonster.com said:
Tom,

This function does not take into account days, only months. The DateDiff
function will return only whole numbers, therefore if somebody's birthday
was on the 31st of March and you compared it with the 25th of March
(today)
, it would return 0. This would be fine in this instance, but reverse it
and you see you have a problem - somebody whose birthday is the 10th of
March would still return 0, and therefore return their birthday this year.
Click to expand...

Have you actually tried it? It seems to work for me no matter what order I
put them in.

If your birthday is 3/25 and today is 3/31/2005 it gives 3/25/2005.
If your birthday is 3/31 and today is 3/25/2005, it gives 3/31/2005.
If your birthday is 3/10 and today is 3/25, it gives 3/10/2005 since that's
the nearest birthday to 3/10 in the past, no?

What should the three examples above return?


I did see a discrepancy as far as declaring variables but it didn't give an
error in my copy of Access 2002. Below is the correction.

Function NearestAgeChange(Birth As Date) As Date
Dim BDayThisYear As Date
Dim BDayLastYear As Date
BDayThisYear = DateSerial(Year(Date), Month(Birth), Day(Birth))
BDayLastYear = DateSerial(Year(Date) - 1, Month(Birth), Day(Birth))
If DateDiff("m", Date, BDayThisYear) <= 6 Then
NearestAgeChange = BDayThisYear
Else
NearestAgeChange = BDayLastYear
End If
End Function

Tom Lake
 
Tom

I haven't tested your code but I think there may be a logic problem if the
BirthDate is, says, 01/Jan/1960 and today's date were, says, 01/Sep/2005.
In this case, the NearestAgeChange should be 01/Jan/2006 and I am fairly
sure won't return 01/Jan/2006 (possibly returning 01/Jan/2004???).

I guess the function should be something like:

****Untested****
Function NearestAgeChange(BirthDate As Date) As Date
Dim LastBirthDay As Date
Dim NextBirthDay As Date

If Format(BirthDate, "mmdd") < Format(Date(), "mmdd") Then
LastBirthDay = DateSerial(Year(Date), Month(Birth), Day(Birth))
NextBirthDay = DateSerial(Year(Date) + 1, Month(Birth), Day(Birth))
Else
LastBirthDay = DateSerial(Year(Date)-1, Month(Birth), Day(Birth))
NextBirthDay = DateSerial(Year(Date), Month(Birth), Day(Birth))
End If

If DateDiff("d", LastBirthDay, Date() ) < DateDiff("d", Date(),
NextBirthDay) Then
NearestAgeChange = LastBirthDay
Else
NearestAgeChange = NextBirthDay
End If
End Function
********

The O.P. should check which way he wants to go if today's date is exactly
mid-point between the 2 birthdays.
 
Tom,

Ignore me - I can see now that it would work, I had simply been up too long
and drunk too much wine last night, unfortunately a trick I have inherited
from my father I believe.
 
BIG, BIG thanks to John, Van, and especially Tom. You guys are simply
amazing!

Unfortunately, none of these formula's is exactly what i'm looking
for. Perhaps it's my fault...maybe I didn't state my case clearly
enough. If you would be so kind as to give it another try, it would be
MOST appreciated.

As I mentioned, insurance premiums are based on your NEAREST birthday.
I have the client's birthdate [BIRTHDATE], but I'm trying to create a
2nd field labeled "Nearest Age Change." This will ALWAYS be the
client's nearest HALF birthday. This is when the insurance company
views his age as actually changing. I'm trying to give myself a way to
determine at a glance if he has an age change coming up soon, or if he
recently already past one.

Here are some examples. All assume today's date to be 3/25/2005:

Client's Bday is 5/16/1960. His nearest age change was 11/17/2004.
Client's Bday is 11/17/1960. His nearest age change will be 5/18/2005.
Client's Bday is 8/17/1936. His nearest age change was 2/18/2005.
Client's Bday is 10/29/1929. His nearest age change will be 4/30/2005.
Client's Bday is 3/24/1970. His nearest age change was 9/25/2004.

I hope this clarifies thing a bit. I'm counting on you guys!
Thanks in advance.

Paul
 
Try:

********
Public Function NearestAgeChange(BirthDate As Date) As Date
Dim LastBirthDay As Date

If Format(BirthDate, "mmdd") < Format(Date, "mmdd") Then
LastBirthDay = DateSerial(Year(Date), Month(BirthDate), Day(BirthDate))
Else
LastBirthDay = DateSerial(Year(Date) - 1, Month(BirthDate),
Day(BirthDate))
End If

NearestAgeChange = DateAdd("d", 1, DateAdd("m", 6, LastBirthDay))

End Function
********

Results from Immediate window:

?NearestAgeChange(#5/16/1960#)
17/11/2004

?NearestAgeChange(#11/17/1960#)
18/05/2005

?NearestAgeChange(#8/17/1936#)
18/02/2005

?NearestAgeChange(#10/29/1929#)
30/04/2005

?NearestAgeChange(#3/25/1970#)
26/09/2005

?NearestAgeChange(#3/26/1970#)
27/09/2004

?NearestAgeChange(#3/27/1970#)
28/09/2004

?NearestAgeChange(#3/28/1970#)
29/09/2004

(My today's date is 26/03/2005 and fomat is dd/mm/yyyy.)

The only difference is your last example but logically, if someone just had
his/her birthday, the nearest mid-point of birthdays is in the future, not
the past. Conversely, for someone whose birthday is coming up shortly, the
nearest mid-point is in the past.
 
This equation LOOKS great. That's why I know this next question is
going to make me seem like an idiot, but how do I insert it into my
form to make it work? If tried a dozen different things, but I must
be missing something really obvious because I just can't get it to
work. Would someone be willing to give me a brief (very brief)
tutorial?

Thanks so much,

Paul
 
If you simply want to display this date in your form, and your form is bound
to a recordsource which includes the date of birth , all you need to do is
create an unbound textbox and set its control source as:
=NearestAgeChange(NameOfDateOfBirthField)

If you are showing the dateof birth in a textbox called tboxDateOfBirth, you
could put:
=NearestAgeChange(tboxDateOfBirth)

HTH,

Rob

poochbeast said:
This equation LOOKS great. That's why I know this next question is
going to make me seem like an idiot, but how do I insert it into my
form to make it work? If tried a dozen different things, but I must
be missing something really obvious because I just can't get it to
work. Would someone be willing to give me a brief (very brief)
tutorial?

Thanks so much,

Paul
Click to expand...
<snip>
 
Rob said:
If you simply want to display this date in your form, and your form is bound
to a recordsource which includes the date of birth , all you need to do is
create an unbound textbox and set its control source as:
=NearestAgeChange(NameOfDateOfBirthField)

If you are showing the dateof birth in a textbox called tboxDateOfBirth, you
could put:
=NearestAgeChange(tboxDateOfBirth)

HTH,

Rob



<snip>
Click to expand...

You may need to put the call in the OnCurrent event of the form and the
AfterUpate Event of the birthdate control.

Me!NearestDate = NearestAgeChange(tboxDateOfBirth)

In the event the birthdate gets changed another way such as a datasheet
view of the table or an update query or the user changed the date in this
form, you will need to recalc the nearest date again.

Ron
 
Back
Top