-=On 26 Jul 96, Jan Willem Stad said to All about Calculating age:
 JW> Does anyone have any suggestions on how to create a field in Access 7
 JW> which calculates age?
 JW> It has to be something like: After you type a birthdate in a field in
 JW> another field the current date has to be substracted from this
 JW> birthdate. But how to accomplish this?
INF: Two Functions to Calculate Age in Months and Years
Article ID: Q100136
---------------------------------------------------------------------
The information in this article applies to:
 - Microsoft Access versions 1.0, 1.1, and 2.0
---------------------------------------------------------------------
SUMMARY
=======
This article explains how to create a function to calculate the age of
a person or thing based on a given date.
This article assumes that you are familiar with Access Basic and with
creating Microsoft Access applications using the programming tools provided
with Microsoft Access. For more information on Access Basic, please refer
to the "Introduction to Programming" manual in Microsoft Access version
1.x, or the "Building Applications" manual, Chapter 3, "Introducing Access
Basic" in version 2.0.
MORE INFORMATION
================
Enter the following code in a module:
NOTE: In the following sample code, an underscore (_) is used as a line-
continuation character. Remove the underscore when re-creating this code in
Access Basic.
   '==========================================================
   ' General Declaration
   '==========================================================
   Option Explicit
   '*************************************************************
   ' FUNCTION NAME: Age()
   '
   ' PURPOSE:
   '    Calculates age in years from a given date to today's date.
   '
   ' INPUT PARAMETERS:
   '    StartDate: The beginning date (for example, a birth date).
   '
   ' RETURN
   '    Age in years.
   '*************************************************************
   Function Age (varBirthDate As Variant)
      Dim varAge As Variant
      If IsNull(varBirthdate) then Age = 0: Exit Function
      varAge = DateDiff("yyyy", varBirthDate, Now)
      If Date < DateSerial(Year(Now), Month(varBirthDate),_
                           Day(varBirthDate)) Then
         varAge = varAge - 1
      End If
      Age = varAge
   End Function
   '*************************************************************
   ' FUNCTION NAME: AgeMonths()
   '
   ' PURPOSE:
   '    Compliments the Age() function by calculating the number of months
   '    that have expired since the last month supplied by the given date.
   '    If the given date is a birthday, the function returns the number of
   '    months since the last birthday.
   '
   ' INPUT PARAMETERS:
   '    StartDate: The beginning date (for example, a birthday).
   '
   ' RETURN
   '    Months since the last birthday.
   '*************************************************************
   Function AgeMonths(ByVal StartDate As String)
      Dim tAge As Double
      tAge = (DateDiff("m", StartDate, Now))
      If (DatePart("d", StartDate) > DatePart("d", Now)) Then
          tAge = tAge - 1
      End If
      If tAge < 0 Then
         tAge = tAge + 1
      End If
      AgeMonths = tAge Mod 12
   End Function
Testing the Age() and AgeMonths() Functions
-------------------------------------------
The following steps describe how to test the Age() and AgeMonths()
functions:
1. Open a module or create a new one.
2. From the View menu, choose Immediate Window.
3. Assume your friend's birth date was November 15, 1967 and today is
   June 3, 1993. Type the following in the Immediate window and press
   ENTER:
      ? Age("11/15/67")
   Microsoft Access responds with the value 25 (years).
4. Type the following and press ENTER:
      ? AgeMonths("11/15/67")
   Microsoft Access responds with the value 6, indicating that six
   months have passed since this person's last birthday. Your friend
   is 25 years and six months old.
Using the Age() and AgeMonths() Functions
-----------------------------------------
The following procedure explains how to mark old orders by placing the
age value in a new control:
1. In the sample database NWIND.MDB, enter the Age() and AgeMonth()
   functions in a new module.
2. Open the Orders form in Design view and add an unbound text box.
3. Type the following in the ControlSource property of the new
   control:
      =Age([Order Date]) & " ys " & AgeMonths([Order Date]) & " mo"
4. Switch to Form view. The age of the order is displayed in the new
   control.
Additional reference words: 1.00 1.10 2.00 birth birthday birthdate age abc
KBCategory: kbprg
KBSubcategory: PgmOthr
Copyright 1993 Microsoft Corporation. All rights reserved.
--- Win-Mail v0.10a/BETA
---------------