How can I get an accurate age to display in an Access form?

see this link

Calculate Age in Months and Years
http://support.microsoft.com/?kbid=210522

see this similar threads

https://www.experts-exchange.com/questions/20881168/finding-a-persons-age-by-his-birthday.html

https://www.experts-exchange.com/questions/21265648/Calculate-age-by-birthday-in-years-months-and-days.html

Have you put it in a control source?
also what it's giving you is the number of days.  

Probably the best discussion on Age calcs ever"

https://www.experts-exchange.com/questions/22582424/Age-calculation-in-form-using-DLookup.html

mx

The formula I listed is in the Control Source Field in the Proprties window.

Your formula as NeoX81 mentioned is simply returning the number of days between Date () and the DOB date.

mx

If all you need is age in years, can't you just use DATEDIFF?
DATEDIFF(YYYY, NOW, DOB)

Ok, but why is it working properly with ages over 30?

"DATEDIFF(YYYY, NOW, DOB)" is this EXACTLY what I would enter into the Control Source Field in properties?

Unfortunately, DATEDIFF("YYYY", NOW(), DOB) does not take into account the month you are born, so ... for example:

DATEDIFF("YYYY", NOW(), #1-1-1945#) = 62

as does

DATEDIFF("YYYY", NOW(), #12-31-1945#) =62

Obviously no one is 62 all year long !

I **highly** recommend you read all of the info in this thread:

https://www.experts-exchange.com/questions/22582424/Age-calculation-in-form-using-DLookup.html

mx

I read through that thread and it is WAY over my head.  

I was hoping someone might have simple instructions on how to set up the coding for a cmd button so I can launch an ADDRESSED email from an Access form.

Sorry, typo.

I read through that thread and it is WAY over my head.  

I was hoping someone might have simple instructions on how to set up a formula or the coding so I can fix my age problem.

In the Control Source of you text box ... this will cover most cases, except maybe the Feb 29 case

=datediff("yyyy",[txtDOB2],date())+(month(date()) < month([txtDOB2]))

mx

Thanks, I will try that in the morning.

Why not now ?

mx

I will need to make a backup copy of the program before I make any changes and I don't want to do that through the VPN.

ok ... no problem.

mx

When DatabaseMX gets ofahn's age problem fixed, can I hire him to work on my age problem. I pay very well for good results. When I put in my DOB, it comes back and tells me I am 57. I want to be about 34!!! It's worth 500 points ;-)

what is your DOB ?

mx

DatabaseMX you don't need dbbishop dob, just get day/month and for year use year of (Date() - 34) he'll always be somewhere 33-35 then eheheh

11/28/1949

?datediff("yyyy",#11/28/1949#,date())+(month(date()) < month(#11/28/1949#)) = 57

OUCH !!!

Don't ya hate it when a formula is correct, lol !!


mx

Oh no, not again.

> I read through that thread and it is WAY over my head.  

Then concentrate on the solution, which should be no more than a simple copy and paste for you, and then your age calculation will be 100% correct:

https://www.experts-exchange.com/questions/22582424/Age-calculation-in-form-using-DLookup.html#19348488

> I was hoping someone might have simple instructions on
> how to set up a formula or the coding so I can fix my age problem.

There is none simpler. If you doubt this, study that thread.

/gustav

DatabaseMX,

I tried the coding you gave me and the field comes up blank.

Do you have any other suggestions?

DatabaseMX: It needs to always return 34.

": It needs to always return 34."

Huh ???

"Do you have any other suggestions?"

What is the exact expression you have and where is it located?

mx

I can't get this code to work

=datediff("yyyy",[txtDOB2],date())+(month(date()) < month([txtDOB2]))

Does anyone have any ideas to fix the problem.

I fully tested the expression before I posted it.  

What is an example of the data in [txtDOB2] ?
Is the Control Source for that text box a Date/Time data type? or a String?

mx

Are you familiar with VBA and events?

In your control in the events for when you enter in bday select the onupdate (I think) double click it, should open to vba editor.  

Also copy the code that's posted from the link a few post above in and just make a function call to it

should look something like this.

private sub onupdate
    (label you want to display the age).text = Years(now(), DOB)
end sub

Public Function Years( _
  ByVal datDate1 As Date, _
  ByVal datDate2 As Date, _
  Optional ByVal booLinear As Boolean) _
  As Integer

' Returns the difference in full years between datDate1 and datDate2.
'
' Calculates correctly for:
'   negative differences
'   leap years
'   dates of 29. February
'   date/time values with embedded time values
'   negative date/time values (prior to 1899-12-29)
'
' Optionally returns negative counts rounded down to provide a
' linear sequence of year counts.
' For a given datDate1, if datDate2 is decreased step wise one year from
' returning a positive count to returning a negative count, one or two
' occurrences of count zero will be returned.
' If booLinear is False, the sequence will be:
'   3, 2, 1, 0,  0, -1, -2
' If booLinear is True, the sequence will be:
'   3, 2, 1, 0, -1, -2, -3
'
' If booLinear is False, reversing datDate1 and datDate2 will return
' results of same absolute value, only the sign will change.
' This behaviour mimics that of Fix().
' If booLinear is True, reversing datDate1 and datDate2 will return
' results where the negative count is offset by -1.
' This behaviour mimics that of Int().

' 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.
'
' 2000-11-03. Cactus Data ApS, CPH.
' 2000-12-16. Leap year correction modified to be symmetrical.
'             Calculation of intDaysDiff simplified.
'             Renamed from YearsDiff() to Years().
' 2000-12-18. Introduced cbytMonthDaysMax.
' 2007-06-22. Version 2. Complete rewrite.
'             Check for month end of February performed with DateAdd()
'             after idea of Markus G. Fischer.

  Dim intDiff   As Integer
  Dim intSign   As Integer
  Dim intYears  As Integer
 
  ' Find difference in calendar years.
  intYears = DateDiff("yyyy", datDate1, datDate2)
  ' For positive resp. negative intervals, check if the second date
  ' falls before, on, or after the crossing date for a full 12 months period
  ' while at the same time correcting for February 29. of leap years.
  If DateDiff("d", datDate1, datDate2) > 0 Then
    intSign = Sgn(DateDiff("d", DateAdd("yyyy", intYears, datDate1), datDate2))
    intDiff = Abs(intSign < 0)
  Else
    intSign = Sgn(DateDiff("d", DateAdd("yyyy", -intYears, datDate2), datDate1))
    If intSign <> 0 Then
      ' Offset negative count of years to continous sequence if requested.
      intDiff = Abs(booLinear)
    End If
    intDiff = intDiff - Abs(intSign < 0)
  End If
 
  ' Return count of years as count of full 12 months periods.
  Years = intYears - intDiff
 
End Function

What is an example of the data in [txtDOB2] ?
A whole number (exp. 26 or 34)

Is the Control Source for that text box a Date/Time data type? or a String?
What is the difference?

This is what is in the Control Source line; however, it won't properly show an age less than 30 or so
=Date()-[DOB1]

NeoX81:

I don't have a CLUE how to work with that code.

At this point I am cutting and pasting EXACTLY what I need into my coding to make things work.  If some one does not tell me EXACTLY where to paste the code and how to edit it I will be lost.

Sorry.  txtDOB2 implied to me that (DOB) this text box had a Date of Birth ... like 2-7-1968.  And if it did, the expression I posted calculates the Age ... which I what I thought you were trying to do?

So ... I am confused ....

mx

Yes, you are right.  I misunderstood the question as "what age are we trying to display?"

The DOB is entered as 2/1/1980; but I can't get your code to work.  Perhaps it is because I don't understand how to add it to the Control Source and am leaving something out.

DatabaseMX

I just entered your code again and the field comes up blank.

ok .... back to your original Q:

" The query uses this formula to calculate age "=Date()-[txtDOB2]";

Are you wanting to display the Age in a query ... OR ... on a form?

mx

1) Create a job aid that stays at the computer the data entry will be going into.
2) Create job-aid for using calculater.
3) Post a calendar.
4 User enters date of birth into form.
5) User enters year of current year (note calendar) into calculator using keypad.
6) User presses the '-' key.
7) User enters the year the person was born into the calculator using keypad.
8) User presses the [Enter] key and redords the answer on a clean white piece of paper that will later be accessible.
8) Use enters the month of the date of birth into the calculator using keypad.
9) User presses "-" key
10) User enters month of current date (from calander) into calculator.
11) If the answer is less than 1, the user enters the answer that was written in step 8 into the Age textbox. Processing in complete.
12 ) If answer was 0 or more than 0, then the user enters the person's day of birth from their birthdate (e.g. 07/[17]/1873--17 is what is entered) in the calculator.
13 ) User presses the "-" key on the calculator.
14) User enters the day value from the calendar (e.g. 09/[18]/2007) into the calculater. Make note on the jodaid that a request for day of month is looking for a numerical value in the range of 1-31, and not Monday, Tuesday, etc. This is a very important note to have as it will save calls to IT about a program that is not working properly.
15) User presses [Enter] key on calculator.
16) If the value is less than 0, then add 1 to the value that was written down in step 8 above. If necessary, the calulator can be used for determining the actual age, and in most cases will probably be more correct than the average data entry clerk, To do so, first enter the number 1 into the calculator.
17) Press the '-' key.
18) Enter the value into the calculator that was written down in step 8 above. If your dog ate it, start over with step 1.
19) Press [Enter].
20) Write the resulting value down on your piece of paper. Cross out the fiorst number so you don't confuse the two entries.
21) Hightail it to the computer and enter that value into the age textbox.

see job-aid 43295.78.aq2, revison 87f for instructions on entered age into application.
13 User presses "-" key on calculator.

Increment all bullets by 1 starting with the second 8. I will prepare a job aid shortly on how to accomplish that task.

dbbishop:

WHAT ... is that all about ?

mx

A job aiod for getting a persons age. Very detailed. Should work every time :-)

"aiod"

??

Are we on the same Q ????

The age is displayed on a form.

The formula that I posted is in the Control Source field of the properties window for the Age field of the form.

ok then ...

what is the Name of the text box that contains the DOB ?

and

what is the name of the text box that you want the Age displayed in ?

mx

txtDOB1

txtAge1

Put this expression - including the equals sign - in the Control Source of txtAge1:

=datediff("yyyy",[txtDOB1],date())+(month(date()) < month([txtDOB1]))

before ... we had txtDOB2   ... note the two (2) ... you are now saying one (1), right ?

mx

Every DOB I enter into the DOB field now shows 1900 in the age field.

ok ... try this mod:


=datediff("yyyy",CDate([txtDOB1]),date())+(month(date()) < month(CDate([txtDOB1])))

wrapping CDate() around what you are entering ...

mx

I inserted the code but it didn't change anything.

What does

wrapping CDate() around what you are entering ...

mean?

It should insure that what you are entering as a 'date' actually get interpreted as a Date.

I'm stumped ... without looking at it .... so .... I will look at it tomorrow if you can upload.

Can you upload to http://www.ee-stuff.com/Expert/Upload/upload.php ... removing any sensitive data of course?  

Then, provide me the link EE-Stuff gives you to the file location ... back here in this thread.

Note: There is a  4MB upload limit.

mx

I have tried four times to get my database (as a zipped file) uploaded through the link you gave me but it won't take it.

Would you be wiilling to download it from a page on my website?

"Would you be wiilling to download it from a page on my website?"

Yes .... as long as you post that link here ... so that other *could* download it also.

mx

DatabaseMX

here is the link to my website page

http://www.mresources.org/DatabaseMX.html

The db is fully functional although I have removed most of the records to reduce the size.

The form is "Lead Lookup" and the field is "Age".

If this becomes complicated to the point that someone needs to call me to work it out I have an 800 number good from the US or I am willing to provide a prepaid calling card that would usable for international calls.

Thanks,

Eric

Eric,

Create a new module, copy and paste the code below (between the marking lines) into this, compile and save it as, say, basAge or another name of your liking.

Then, in your form, change the Format property of the textbox to Fixed and zero decimals.
Enter this as ControlSource:

=IIf([DOB1] Is Null,Null,Age([DOB1];Date()))

Save the form.
Now open your form and input a DOB.

/gustav

'-----
Public Function Age( _
  ByVal datDateOfBirth As Date, _
  Optional ByVal varDate As Variant) _
  As Integer

' Calculates age at today's date or at a specified date earlier or later in time.
' Uses Years() for calculating difference in years.
'
' Will return 0 for any varDate of the first year before datDateOfBirth.
' See comments for Years().
'
' 2000-11-03. Cactus Data ApS, CPH.
' 2007-06-23. Supplemented with AgeLinear for count of negative ages offset by -1.

  Dim datDate As Date
 
  ' No special error handling.
  On Error Resume Next
 
  If IsDate(varDate) Then
    datDate = CDate(varDate)
  Else
    datDate = Date
  End If

  Age = Years(datDateOfBirth, datDate)
 
End Function

Public Function Years( _
  ByVal datDate1 As Date, _
  ByVal datDate2 As Date, _
  Optional ByVal booLinear As Boolean) _
  As Integer

' Returns the difference in full years between datDate1 and datDate2.
'
' Calculates correctly for:
'   negative differences
'   leap years
'   dates of 29. February
'   date/time values with embedded time values
'   negative date/time values (prior to 1899-12-29)
'
' Optionally returns negative counts rounded down to provide a
' linear sequence of year counts.
' For a given datDate1, if datDate2 is decreased step wise one year from
' returning a positive count to returning a negative count, one or two
' occurrences of count zero will be returned.
' If booLinear is False, the sequence will be:
'   3, 2, 1, 0,  0, -1, -2
' If booLinear is True, the sequence will be:
'   3, 2, 1, 0, -1, -2, -3
'
' If booLinear is False, reversing datDate1 and datDate2 will return
' results of same absolute value, only the sign will change.
' This behaviour mimics that of Fix().
' If booLinear is True, reversing datDate1 and datDate2 will return
' results where the negative count is offset by -1.
' This behaviour mimics that of Int().

' 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.
'
' 2000-11-03. Cactus Data ApS, CPH.
' 2000-12-16. Leap year correction modified to be symmetrical.
'             Calculation of intDaysDiff simplified.
'             Renamed from YearsDiff() to Years().
' 2000-12-18. Introduced cbytMonthDaysMax.
' 2007-06-22. Version 2. Complete rewrite.
'             Check for month end of February performed with DateAdd()
'             after idea of Markus G. Fischer.

  Dim intDiff   As Integer
  Dim intSign   As Integer
  Dim intYears  As Integer
 
  ' Find difference in calendar years.
  intYears = DateDiff("yyyy", datDate1, datDate2)
  ' For positive resp. negative intervals, check if the second date
  ' falls before, on, or after the crossing date for a full 12 months period
  ' while at the same time correcting for February 29. of leap years.
  If DateDiff("d", datDate1, datDate2) > 0 Then
    intSign = Sgn(DateDiff("d", DateAdd("yyyy", intYears, datDate1), datDate2))
    intDiff = Abs(intSign < 0)
  Else
    intSign = Sgn(DateDiff("d", DateAdd("yyyy", -intYears, datDate2), datDate1))
    If intSign <> 0 Then
      ' Offset negative count of years to continuous sequence if requested.
      intDiff = Abs(booLinear)
    End If
    intDiff = intDiff - Abs(intSign < 0)
  End If
 
  ' Return count of years as count of full 12 months periods.
  Years = intYears - intDiff
 
End Function

'-----

> The age now displays correctly.

Well, mx, mostly but not always.
You miss:
   dates of 29. February

/gustav

Thanks,

I will have to wait about an hour to be open the program in design view so I can make the changes.  I will let you know the results.

gustav ... note in my last post above:

"Again, keep in mind ... the function above is not perfect (issue with Feb29).  If you want the ultimate Age function, the use gustav's code ...  and then place"

:-)

mx

I know, but this seems to be a serious application dealing with insurance and so on. That leaves no room for errors.

If you were listing ages of dogs in a kennel club or were mailing greeting cards for kids, the one-liner would be fine.

/gustav

I'm not disagreeing ... not sure what else I can say.

mx

If you want to keep it simple, you can use:

=(Date()-[DOB1])/365.25

Remove the "yy" from format.

Flyster

"=(Date()-[DOB1])/365.25"

Not really accurate. Lets say my DOB is 9-21-1945.  I would be 62 today.

In the vba Immediate window:

?(Date()-#9-21-1945#)/365.25    returns  61.9986310746064

mx

Let me correct myself. I normally use this in a query:

Age:(Now()-[DOB1])/365.25

Set format to General Number. 9/21/45 returns 62.0008520293

Flyster

A more accurate constant is 365.242199

DatabaseMX,

The code you gave still will not give me an accurate age for younger people.

Try the code with this DOB (01/01/1980) and see what you get.  I am getting 1900.

I get 27

You **need** to remove the 'yy' from the Format property in txtAge1 ... that is why you are getting 1900.  If I put yy as the Format, I get 1900.

mx

DatabaseMX

OOPS!  I forgot to pull the yy from the format.

YEAH!  Now it works.  Thanks foe hanging in there.  How do I award more points for this?

You are welcome.  2K pts is the max.  Not to worry.

Please remember however the limitation of that function regarding Feb 29.  Oddly enough, I do know one person whose DOB is  Feb29 ... and he celebrates his birthday on Feb 28 - which he considers to be his DOB.

mx

I am going to try to use the code that will allow me to address 2/29; however, I sy=till have a lot more now that I did.  Again, thanks.

"I am going to try to use the code that will allow me to address 2/29"

ok good ... and in that case, when you get a chance, please re-open the Q and split the pts with Cactus D  ... since that is his function, ok ...

mx

Eric, you didn't follow the recipe:

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

Flyster, please read up the referenced thread:

https://www.experts-exchange.com/questions/22582424/Age-calculation-in-form-using-DLookup.html

/gustav