<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1170" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>For, the record, this is what I ended up
with. It's pretty much Gustav's function, but I just tweaked it a bit on
naming and minor things like that. I also made it return a variant,
since there are situations where I would want it to return null.
</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>The goal of this function is to return an age in
integer years, as in my birthday is July 10, so I'm 35 on July 9, and 36 on July
10. I have been unable to make this function return an incorrect value,
assuming I don't give it impossible data. If a person is born Feb 29, it
uses Feb 28 for the birthday in non leap years. Works for me.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Public Function CalculateAge(varBirth As Variant,
varEndDate As Variant) As Variant<BR>'This function will calculate age from
birthday<BR>' Just using datediff rounds up a year<BR>'Variants are used, since
this is also called from queries</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>' Thanks to Gustav Brock, Cactus Data
ApS.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>On Error GoTo eh</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2> Dim datBirth As
Date<BR> Dim datEnd As Date<BR>
<BR> If IsDate(varBirth) = False
Then<BR> CalculateAge =
Null<BR> GoTo ex<BR>
End If<BR> <BR> If IsDate(varEndDate) =
False Then<BR> CalculateAge =
Null<BR> GoTo ex<BR>
End If<BR> <BR> datBirth =
CDate(varBirth)<BR> datEnd =
CDate(varEndDate)<BR> <BR> CalculateAge =
CalculateYears(datBirth, datEnd)<BR>ex:<BR> Exit
Function<BR>eh:<BR> CalculateAge = -999<BR>
Resume ex<BR>End Function</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>Public Function CalculateYears(ByVal datDate1 As
Date, ByVal datDate2 As Date) As Integer</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>' Returns the difference in full years between
datDate1 and datDate2.<BR>'<BR>' Calculates correctly for:<BR>'
negative differences<BR>' leap years<BR>' dates of 29.
February<BR>' date/time values with embedded time
values<BR>' negative date/time values (prior to
1899-12-29)<BR>'<BR>' Thanks toGustav Brock, Cactus Data ApS.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>On Error GoTo eh</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2> ' Constants for leap year
calculation. Last normal date of February.<BR> Const
cbytFebMonth As Byte = 2<BR>
Const cbytFebLastDay As Byte = 28<BR> '
Maximum number of days in a month.<BR> Const
cbytMonthDaysMax As Byte = 31<BR> <BR>
Dim intYears
As Integer<BR> Dim
intDaysDiff As
Integer<BR> Dim
intReversed As
Integer<BR> <BR> <BR>
intYears = DateDiff("yyyy", datDate1, datDate2)<BR> If
intYears = 0 Then<BR> ' Both dates fall within the
same year.<BR> Else<BR>
<BR> ' Check for ultimo February and
leap years.<BR> If (Month(datDate1) =
cbytFebMonth) And (Month(datDate2) = cbytFebMonth)
Then<BR> '
Both dates fall in
February.<BR>
' Check if dates are at ultimo
February.<BR>
<BR> If
(Day(datDate1) >= cbytFebLastDay) And (Day(datDate2) >= cbytFebLastDay)
Then<BR>
' Both dates are at ultimo
February.<BR>
' Check if the dates fall in leap
years.<BR>
<BR>
If Day(DateSerial(Year(datDate1), cbytFebMonth + 1, 0)) = cbytFebLastDay Xor
_<BR>
Day(DateSerial(Year(datDate2), cbytFebMonth + 1, 0)) = cbytFebLastDay
Then<BR>
' Only one date falls within a leap
year.<BR>
' Adjust both dates to day 28 of
February.<BR>
datDate1 = DateAdd("d", cbytFebLastDay - Day(datDate1),
datDate1)<BR>
datDate2 = DateAdd("d", cbytFebLastDay - Day(datDate2),
datDate2)<BR>
Else<BR>
' Both dates fall either in leap years or non leap
years.<BR>
' No adjustment
needed.<BR>
End
If<BR>
<BR> End
If<BR>
<BR> End
If<BR>
<BR> ' Calculate day difference using
months and days as Days() will fail
when<BR> ' comparing leap years with
non leap years for dates after
February.<BR> intDaysDiff =
(Month(datDate1) * cbytMonthDaysMax + Day(datDate1)) - (Month(datDate2) *
cbytMonthDaysMax + Day(datDate2))<BR>
intReversed = Sgn(intYears)<BR> '
Decrease count of years by one if dates are closer than one
year.<BR> intYears = intYears +
(intReversed * ((intReversed * intDaysDiff) >
0))<BR> <BR> End
If<BR> <BR> CalculateYears =
intYears<BR>ex:<BR> Exit Function<BR>eh:<BR>
CalculateYears = -999<BR> Resume ex<BR>End
Function<BR></FONT></DIV>
<DIV><BR>Mark Whittinghill<BR>Symphony Information
Services<BR>612-333-1311<BR><A
href="mailto:mwhittinghill@symphonyinfo.com">mwhittinghill@symphonyinfo.com</A></DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px"> </BLOCKQUOTE></BODY></HTML>