# Thread: Replace calculated age with text on form.

1. Registered User
Join Date
Sep 2010
Posts
96

## Answered: Replace calculated age with text on form.

Hello all and a belated Happy New Year.

I have a seemingly simple problem that I can't seem to sort out and am looking for some solutions.

In my DB there is a Date Of Birth Field (Date/Time)

I am calculating the persons age using
Code:
`= Year(Now()) - Year([Date Of Birth]) + (DateSerial(Year(Now()), Month([Date Of Birth]), Day([Date Of Birth])) > Now())`
and displaying the result on a form as the control source for a TextBox called Age.

This works fine.

What I now want to do is have one of 3 values displayed in another TextBox on the form called AgeGroup.

The AgeGroup will be either "Infant", "Junior" or "Senior" based on the following criteria;

Infant <11
Junior >= 11 and <30
Senior >=30

My searches over the last couple of days have given me tantalizing glimpses of how to achieve this but I can't seem to get it right.

Can someone kindly point me in the right direction please.

## "Use an IIF statement If ([boolean expression], [value if true], [value if false]) You can 'compound' IIF statements, replace the [value if false] with further IIF statements. The boolean expression means any statement which results in a boolean value of true or false The value segments can be calculated or literals Eg IIf (A < 11,"Junior", IIF ( A >= 30, "Senior", "Junior")) You may need to wrap some form of error handler if you dont know the persons date of birth, or add that error handle to the compound IIF."

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Use an IIF statement
If ([boolean expression], [value if true], [value if false])
You can 'compound' IIF statements, replace the [value if false] with further IIF statements.
The boolean expression means any statement which results in a boolean value of true or false
The value segments can be calculated or literals

Eg
IIf (A < 11,"Junior", IIF ( A >= 30, "Senior", "Junior"))

You may need to wrap some form of error handler if you dont know the persons date of birth, or add that error handle to the compound IIF.

4. Registered User
Join Date
Sep 2010
Posts
96
Hello Healdem

So if I understand you correctly I could use

Code:
```Dim AgeGroupValue as Integer
AgeGroupValue = Year(Now()) - Year([Date Of Birth]) + (DateSerial(Year(Now()), Month([Date Of Birth]), Day([Date Of Birth])) > Now())
IIF (AgeGroupvalue<11,"Infant", IIF(AgeGroupValue >=30,"Senior","Junior"))```
Now if I understand the code correctly it reads

If AgeGroupValue <11 then display "Infant"
If AgeGroupValue >= 30 then Display "Senior"
otherwise display "Junior"
Is this correct?
Do i place this in the Control Source of the text field?

My apologies but I haven't had to deal much with dates in the past.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
How you use that is up to you
You could use a function, then call that function in a query, form or report. Any time you could use this sort of process in more than one form or report is a good candidate for a function.
If the function is to be called from different forms or reports the function goes into a code module (ie as vba code that usnt associated with a specific form or report. If its only to be used in multiple places in the same report or form in can go in that report or forms code, but id suggest always put functions in a code module. If its to be used in a query it must go in a code module
Another advantage of using a function us that you can force datatypes to be if the correct type by defining the datatypes of the incoming parameter. You can build in error checking. Say the incoming parameter is a blank date or is a value out of range.. because yoy use a write once use many gimes approach if the business requirements change then with a carefully crafted function you change that function and all forms, reports and queries usibg that function automatically benefit from that function. Ferinstance say there is a wish to add a 4th membership lable.. if over 75 then "Pensioner".

If you do use a function yhen there us no need to yse tge iif construct use a standard if or a select case statement

Join Date
Feb 2004
Location
New Zealand
Posts
1,478
Here is a Age function I wrote

Code:
```Function Age(Birth_Date,End_Date)
'***************************************
' Works out the age of to the month
'
'***************************************
Dim Months
Dim Years
Dim Temp
If IsNull(Birth_Date) or Birth_Date ="" Then
Age=0.0
else
Months = DateDiff("m", CDate(Birth_Date), End_Date)
Years = Int(Months / 12)
Temp = Years * 12
If Years = 0 then Years = ""
Age =  Years & "." & Months - Temp
End if
End Function```
to used in a Query

Age:Age([feildnamedate],date())

now it return year.months

so

MyAge = age(#01/01/65#,date()) = 51.0

MyAge = age(#01/01/65#,#12/01/15#) = 50.11

MyAge = age(#01/01/65#,#02/01/16#) = 51.01

7. Registered User
Join Date
Sep 2010
Posts
96