Solved

Calculating Age

Posted on 2011-02-25
41
441 Views
Last Modified: 2012-06-27
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?
0
Comment
Question by:gcgcit
  • 19
  • 16
  • 4
  • +1
41 Comments
 
LVL 75
ID: 34983076
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
 
LVL 57
ID: 34983086

 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

Open in new window

0
 

Author Comment

by:gcgcit
ID: 34983159
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 Comment

by:gcgcit
ID: 34983203
JDettman: which variables should I replace with my textboxes?
0
 
LVL 57
ID: 34983255
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
 
LVL 75
ID: 34983299
"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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 34984889
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

Open in new window


Just use it as ControlSource for your textbox of age:

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

It will update automatically.

/gustav
0
 

Author Comment

by:gcgcit
ID: 34997865
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 Comment

by:gcgcit
ID: 34997898
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 34998223
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
 
LVL 57
ID: 34998279
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 34998322
Sorry about that.

/gustav
0
 

Author Comment

by:gcgcit
ID: 34999041
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 Comment

by:gcgcit
ID: 34999066
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
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 400 total points
ID: 34999212
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
 

Author Comment

by:gcgcit
ID: 34999417
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 34999503
That's right. You have to move the focus off txtDOB to force an update.

/gustav
0
 

Author Comment

by:gcgcit
ID: 34999537
Also any records without a birthdate or approx age currently is showing up as #Error in the age too.
0
 

Author Comment

by:gcgcit
ID: 34999562
Actually retract that last statement... to clarify issue: #Error only occurs on Approx age field, when I delete dates on existing records.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 34999574
Then, for those records, your txtDOB is not Null for some reason.

/gustav
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 34999608
I thought the Approx. Age was a combobox?

/gustav
0
 

Author Comment

by:gcgcit
ID: 34999674
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 34999791
Then do a requery of the combobox at the OnDelete event.

/gustav
0
 

Author Comment

by:gcgcit
ID: 35000678
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 Comment

by:gcgcit
ID: 35000792
When I select from the list in the combobox (because the birthdate is empty)... it won't select.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35001378
Sounds like the control source of the combobox needs some tweaking ...

/gustav
0
 

Author Comment

by:gcgcit
ID: 35017086
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35017187
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
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 100 total points
ID: 35017212

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 Comment

by:gcgcit
ID: 35017685
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35017738
You have a typo ...

/gustav
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35017798
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 Comment

by:gcgcit
ID: 35017905
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 Comment

by:gcgcit
ID: 35017911
cactus_data: this is in a standard module - maybe that's why it doesn't like the Me!
0
 

Author Comment

by:gcgcit
ID: 35017928
changed code to:     With Forms!frmIncidents.sbfrmPeople

works now! :)
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35017934
Yes, then you must use the full form name, sorry.

/gustav
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35017940
Great!

/gustav
0
 

Author Comment

by:gcgcit
ID: 35017987
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35018072
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 Closing Comment

by:gcgcit
ID: 35019825
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35020130
You are welcome!

/gustav
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now