put this function in a general (standard) module:

'~~~~~~~~~~~~~~~~~~~~~~~~~~ GetAge
Function GetAge(pDOB As Date _
   , Optional pDate As Date = 0) As Integer
' Crystal (strive4peace)
'3-20-09
   'get age in years given DOB and the date to take age from
   'if pDate is not specified, the current date is used
   
   If pDate = 0 Or Not IsDate(pDate) Then pDate = Date
   
   GetAge = 0
   If Nz(pDOB, 0) = 0 Then Exit Function
   GetAge = DateDiff("yyyy", pDOB, pDate) _
      + (pDate < DateSerial(Year(pDate), Month(pDOB), Day(pDOB)))
End Function

Select allOpen in new window

To call it from, for instance a query, you need to send both the DOB and the optional parameter.

Field --> TheAge: GetAge( [DOB-fieldname], Date() )

queries require that you send optional parameters but in code and other places, like forms, this is not necessary. If Date is not sent, the function will use today's date. It could be written such that sending date is not required. the reason it does have date is to figure out age as of a certain day, like for a tournament or other event.

**********************************************************
*** How to Create a Standard (General) Module ***

Press Alt-F11 to go to the VBE (Visual Basic Editor)

From the menu in a the Microsoft Visual Basic window:
Insert --> Module

once the code is in the module sheet, from the menu, do -->
Debug,Compile

if there are no syntax/reference errors, nothing will appear to happen -- in this case, nothing happening is good <g>

Make sure to give the module a good name when you save it.  You can have several procedures (Subs and Functions) in a module, which gives you a way to categorize them ... ie: basic procedures that would be useful in any database; procedures that are specific to a particular database; procedures for converting data; etc

~~~~~ also be sure to use Option Explicit at the top of each module so variables that are not declared or are misspelled will be picked up

Option Explicit ' require variable declaration

Select allOpen in new window

So I copied the code and saved it to a module which I called GetAge

Then in the report under design view I have a field called age and under Data-->Control source I have this code: =GetAge([birthdate],Date())

When I try and run the report a box pops up asking for the parameter of GetAge

once the code is in the module sheet, from the menu, do -->
Debug,Compile

and then SAVE -- but do NOT name the module the same as any function or sub -- call it "mod_GetAge" or "bas_GetAge". You can change the Name of the module by showing the properties if it is not already showing -- from the menu: View, Properties window

> "I have a field called age"

If by "field", you really meant "control" then realize that forms and reports contain controls, not fields.

Ok, So I deleted the module and created a new one with that name. Now when I run the report I am getting this for the age: #Type!

I am attaching a couple of screenshots so you can see what I am doing
Module.jpg
Report.jpg

do not store "age" in the table

the equation should be:
=GetAge([birthdate], Date() )

birthdate needs to be stored as date/time data type

be sure do Debug, Compile, and Save the module before you use the function

Ok, I think it is working but it only shows how old they are in years. I would like months included. At least if the child is under 1 it should show months. I don't need it to shows months for anyone over 1 but if it does it's fine as well.

I am a bit confused. What is piMonthsBeforeYear = age in years to STOP also reporting months in age ?
That is the 3rd Variable, correct ? What should I be sending for that ?

if you only want to see months for less than age 2, use 1. If you only want to see months for first year, use 0. Sorry I didn't explain that very well ~

Note that now the function returns a string, not a number

It worked !!! Thank you for your patience.

you're welcome ~ happy to help