Calculating Age

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:

https://www.experts-exchange.com/questions/26629194/Age-calc-in-access.html?cid=1572&anchorAnswerId=34181200#a34181200

mx

 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

Select allOpen in new window

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?

JDettman: which variables should I replace with my textboxes?

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.

"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.  

https://www.experts-exchange.com/questions/22837803/How-can-I-get-an-accurate-age-to-display-in-an-Access-form.html

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

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

Select allOpen in new window

Just use it as ControlSource for your textbox of age:

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

It will update automatically.

/gustav

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

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

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

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.

Sorry about that.

/gustav

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]))

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]))

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

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

/gustav

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

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

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

/gustav

I thought the Approx. Age was a combobox?

/gustav

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)

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

/gustav

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.

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

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

/gustav

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.

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

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

You have a typo ...

/gustav

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

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

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

changed code to:     With Forms!frmIncidents.sbfrmPeople

works now! :)

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

/gustav

Great!

/gustav

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?

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

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!!!

You are welcome!

/gustav