# Calculating Age

I have two fields on a form, one is the birthday (txtDOB) and the other is the age (intApproxAge).  When the birthday is updated, I want the age to calculate in years.

What event should I put this under and what would the syntax be to calculate the birthday for these two fields?
###### Who is Participating?

x

CIOCommented:
You can't really because if you have a DOB, you won't have an age stored - you will calculate it on the fly (=dynamic).

However, if you store a static age, you could pick that should no DOB exist:

=IIf(IsNull([txtDOB]),[MyStoredAge],AgeSimple([txtDOB]))

For the combobox, you disable that using this code line in the OnCurrent and AfterUpdate events of txtDOB:

Me!cboAgeSelect.Enabled = Not IsNull(Me!txtDOB.Value)

/gustav
0

Database ArchitectCommented:
The Age can just be a calculated control - based on the DOB.

=<function call to get age>
See below ...

More than meets the eye in Age calcs ... suggest looking at the Assisted Solution in the following Q - by Cactus Data:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26629194.html?cid=1572#a34181200

mx
0

PresidentCommented:

AfterUpdate event of the birthdate control and you can use the attached function.

JimD.
``````Function GetAge1(varBD As Variant, Optional varAsOfDate As Variant = Null) As Integer

Dim intAge As Integer

10      If Nz(varBD, "") = "" Then varBD = Now()
20      If Nz(varAsOfDate, "") = "" Then varAsOfDate = Now()

30      GetAge1 = DateDiff("yyyy", varBD, varAsOfDate) + (Format(varAsOfDate, "mmdd") < Format(varBD, "mmdd"))

End Function
``````
0

Author Commented:
DatabaseMX: before posting the question I had searched the repository, most I found were debates and couldn't figure out what the final concensus is.  Can you please paste the line of code that works?
0

Author Commented:
JDettman: which variables should I replace with my textboxes?
0

PresidentCommented:
In the AfterUpdate event of txtDOB

Me.intApproxAge = GetAge1(Me.txtDOB,Date())

@MX

Have you tried Gustav's function?  I don't believe it works correctly.  If you use 02/29/2000 for example and the current day is 02/28/2011, it will tell you the person is 11 years old.

Despite what Gustav says, I don't believe it accounts for leap years correctly.

JimD.
0

Database ArchitectCommented:
"Can you please paste the line of code that works?"
It's not just one line of code.  It's a complete Function.

And better yet ... see this Q - not my Accepted answer, but Cactus_Data post @ 09/21/07 05:22 AM, ID: 19934903  ... right above my Accepted Answer.

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22837803.html

@JDettman
I've tried it in the past.  Not lately cuz age calcs just don't come  up that often for me.

0

CIOCommented:
Jim, where have you been?

This has been discussed so many times here for the last 3Â½ years that I stopped counting, and I posted code - functions Years and AgeSimple - numerous times and also at AccessD.

As I have written many times, anyone is free to make their own rules for calculating age of leaplings in non-leap years as to how they "feel" or prefer. However, no official rule exists that support other methods than the one DateAdd also follows - that your birthday, if you are born on a 29th of February, always falls on the last day of February.

In business, like insurance and pension, this can be of extreme importance and there is no reason for going with functions that are nearly right when simple methods exist that does it 100% correct. That's why I called the function AgeSimple:
``````Public Function AgeSimple( _
ByVal datDateOfBirth As Date) _
As Integer

' Returns the difference in full years from datDateOfBirth to current date.
'
' Calculates correctly for:
'   leap years
'   dates of 29. February
'   date/time values with embedded time values
'
' DateAdd() is used for check for month end of February as it correctly
' returns Feb. 28. when adding a count of years to dates of Feb. 29.
' when the resulting year is a common year.
' After an idea of Markus G. Fischer.
'
' 2007-06-26. Cactus Data ApS, CPH.

Dim datToday  As Date
Dim intAge    As Integer
Dim intYears  As Integer

datToday = Date 'datDateCurrent ' Date
' Find difference in calendar years.
intYears = DateDiff("yyyy", datDateOfBirth, datToday)
If intYears > 0 Then
' Decrease by 1 if current date is earlier than birthday of current year
' using DateDiff to ignore a time portion of datDateOfBirth.
intAge = intYears - Abs(DateDiff("d", datToday, DateAdd("yyyy", intYears, datDateOfBirth)) > 0)
End If

AgeSimple = intAge

End Function
``````

Just use it as ControlSource for your textbox of age:

=IIf(IsNull([txtDOB]),Null,AgeSimple([txtDOB]))

It will update automatically.

/gustav
0

Author Commented:
cactus _data: will all the debates in the links I really appreciate you reposting the exact function... curious though if my age combobox currently looks like this (see image) how do I incorporate the code you indicate above "controlsource" - does it then have to be unbound?
ee-approxage.jpg
0

Author Commented:
Below is what my interface looks like.  User enters a date using a calendar control (that pops up) when the date is entered in the first textbox it auto-selects the right number in the combobox.  Because we won't always have the birthday, the user also needs the ability to indicate the approx age if they aren't sure.  This age is actually stored so it's a bound field.
ee-approxage2.jpg
0

CIOCommented:
I would have an unbound and read-only textbox to display the age (as shown above) if/when a Date of Birth is given/entered.

Then I would have another field or combo to enter the Approx. Age if no DOB is given. This could be bound to your table.

Your problem, however, is that entering an age will be the age at that date; thus, what would be better would be to calculate and save the approx. DOB instead - perhaps with an additional boolean field indicating that this DOB is approximated:

ApproxDOB = True
DateOfBirth = DateAdd("yyyy", [txtApproxAge], Date())

Then, should you later obtain the true birthdate, enter this and set ApproxDOB to False.

/gustav
0

PresidentCommented:
gustav,

<<Jim, where have you been?>>

Right here.

All I said was I don't agree with you, which is true.  I realize that people born on Feb 29th may choose to call Feb 28th their birthday or March 1st, but I simply don't feel Feb28th is correct.  To me, logically it makes no sense what so ever.

JimD.
0

CIOCommented:

/gustav
0

Author Commented:
I was thinking about doing enabling and disabling of the combobox... if birthdate exists, disable combobox and assign value... if birthdate doesn't exist they can choose the approxmate age.  Withthis functionality in mind - having 1 unbound field for the age... how do I incorporate the code provided above?

=IIf(IsNull([txtDOB]),Null,AgeSimple([txtDOB]))

0

Author Commented:
Woops sorry... TYPO I meant to type above... having 1 "BOUND" field for age... how do I incoporate the code provided above?

=IIf(IsNull([txtDOB]),Null,AgeSimple([txtDOB]))

0

Author Commented:
Very helpful gustav thank you.

I got most of it working now BUT when I go to delete the date after it's been entered and calculated, I get this (see image) - combobox is still disabled and I get a #Error

I put your line of code on the form Currant and the AfterUpdate of the birthdate textbox
ee-approxage3.jpg
0

CIOCommented:
That's right. You have to move the focus off txtDOB to force an update.

/gustav
0

Author Commented:
Also any records without a birthdate or approx age currently is showing up as #Error in the age too.
0

Author Commented:
Actually retract that last statement... to clarify issue: #Error only occurs on Approx age field, when I delete dates on existing records.
0

CIOCommented:
Then, for those records, your txtDOB is not Null for some reason.

/gustav
0

CIOCommented:
I thought the Approx. Age was a combobox?

/gustav
0

Author Commented:
Birthdate is a textbox
Approx Age is a combobox

when I delete the birthdate after the calculation is done, the approx age combobox has #Error (see image above)
0

CIOCommented:
Then do a requery of the combobox at the OnDelete event.

/gustav
0

Author Commented:
Ok I put a me.requery on the change event of the textbox and now the #Error doesn't show.

One last glitch... ther seems to be something wrong with the controlsource... I can't select from the combobox now and save a value - could the synatx in this be wrong?

=IIf(IsNull([txtDOB]),[intApproxAge],AgeSimple([txtDOB]))

Not sure if this is helpful to note but the combobox does have a green triangle on the corner.
0

Author Commented:
When I select from the list in the combobox (because the birthdate is empty)... it won't select.
0

CIOCommented:
Sounds like the control source of the combobox needs some tweaking ...

/gustav
0

Author Commented:
I have the code you provided in the controlsource:

=IIf(IsNull([txtDOB]),[intApproxAge],AgeSimple([txtDOB]))

It's not allowing me to select and assign a value manually.  What should syntax be to allow someone to manually select from combobox if txtDOB is null.
0

CIOCommented:
You will have to modify your user interface.
A control to enter data into cannot be bound to an expression (=with an equal sign) or to a read-only field.

Either have another control (textbox) unbound or bound to a field that can be updated.
Or use code to set the value of your control after update of txtDOB:

Me!txtAge.Value = IIf(IsNull([txtDOB]),[intApproxAge],AgeSimple([txtDOB]))

/gustav
0

PresidentCommented:

1. Set the controlsource for txtDOB and the combo for approx age to their respective fields in the record.

2. In the AfterUpdate event of txtDOB, do:

If Not IsNull(Me.txtDOB) Then Me.<combo control name> = AgeSimple(Me.txtDOB])

3. In the AfterUpdate event of the combo control, do:

Me.txtDOB = Null

Your other choice here would be to check for an existing date in the field and simply change the year based on the combo.

HTH,
JimD.
0

Author Commented:
JDettman: What is the syntax to put your code above in a standard module (it's being used on a subform)?  This is what I have tried but it says it can't find "sbfrmPeople"

If strCtrl = "txtDOB" Then
If Not IsNull(Forms!sbfrmPeople.txtDOB) Then Forms!subfrmPeople.intApproxAge = AgeSimple(Forms!sbfrmPeople.txtDOB)
Forms!subfrmPeople.intApproxAge.Enabled
End If

0

CIOCommented:
You have a typo ...

/gustav
0

CIOCommented:
Or, to type a little less:

If strCtrl = "txtDOB" Then
With Me!subfrmPeople
If Not IsNull(!txtDOB) Then
!intApproxAge = AgeSimple(!txtDOB)
!intApproxAge.Enabled = False
Else
!intApproxAge.Enabled = True
End If
End With
End If

/gustav
0

Author Commented:
cactus_data: I get error invalid use of Me! on the "With" line.

If strCtrl = "txtDOB" Then
With Me!sbfrmPeople
If Not IsNull(!txtDOB) Then
!intApproxAge = AgeSimple(!txtDOB)
!intApproxAge.Enabled = False
Else
!intApproxAge.Enabled = True
End If
End With
End If
0

Author Commented:
cactus_data: this is in a standard module - maybe that's why it doesn't like the Me!
0

Author Commented:
changed code to:     With Forms!frmIncidents.sbfrmPeople

works now! :)
0

CIOCommented:
Yes, then you must use the full form name, sorry.

/gustav
0

CIOCommented:
Great!

/gustav
0

Author Commented:
Now I just need to get the enabling and disabling working and its done.

On the Form_Current I have this:

If IsNull(Me!txtDOB.Value) Then
Me!intApproxAge.Enabled = True
Else
Me!intApproxAge.Enabled = False
End If

But the intapproxage always the same depending on the first record in the continuous subform.  If the first record has a birthdate then all the approx ages are disabled, if there isn't a birthdate in the first one then they are all enabled.  Any way around this?
0

CIOCommented:
Not in this way.
But you could enable it and use the OnEnter event of intApproxAge to set it to locked or not. If locked, you cannot enter values into the textbox:

Me!intApproxAge.Locked = Not IsNull(Me!txtDOB)

/gustav
0

Author Commented:
Divided points up between both of you because cactus_database provided the majority of solution that expert got more points, but JDettman also provided useful assistance that helped solve it so didn't seem fair to not give him some. Hope you agree with how I balanced points.

Thanks!!!
0

CIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.