424,476 Members | 1,390 Online
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

# generate age

 P: n/a hi, i've got a customer table, with a date of birth field in it. its entered like; 12/12/1992 how would i generate an age through this field. e.g. i've got txtAge. how would i have the correct age inserted in there? thanx dev Jan 22 '06 #1
7 Replies

 P: n/a I would try =DateDiff("d",[txtBirth],Now())/365 as the control source in field txtAge. This assumes the DOB field is called "txtBirth" on the form. This calculates age from TODAY's date, using 365 days per year. Sara Jan 22 '06 #2

 P: n/a "DP" wrote in message news:D6*****************@newsfe6-gui.ntli.net... hi, i've got a customer table, with a date of birth field in it. its entered like; 12/12/1992 how would i generate an age through this field. e.g. i've got txtAge. how would i have the correct age inserted in there? thanx dev cut and paste the function into a new module. Then you could call it by setting the control source of a textbox to =GetAge([MyDateOfBirthField]) Public Function GetAge(BirthDate As Variant) As String On Error GoTo Err_Handler Dim dteDOB As Date Dim lngDays As Long Dim lngMonths As Long Dim lngYears As Long Dim strAge As String strAge = "Error!" If Not IsNull(BirthDate) Then dteDOB = CDate(BirthDate) lngMonths = DateDiff("m", dteDOB, Date) lngDays = DateDiff("d", DateAdd("m", lngMonths, dteDOB), Date) If lngDays < 0 Then lngMonths = lngMonths - 1 lngDays = DateDiff("d", DateAdd("m", lngMonths, dteDOB), Date) End If lngYears = lngMonths \ 12 lngMonths = lngMonths Mod 12 strAge = CStr(lngYears) & " yrs " & _ CStr(lngMonths) & " mths " & _ CStr(lngDays) & " days " Else strAge = "Unknown" End If Exit_Handler: GetAge = strAge Exit Function Err_Handler: Resume Exit_Handler End Function Jan 22 '06 #3

 P: n/a Go to http://groups.google.ca/group/comp.databases.ms-access In the box in the upper right hand corner type Calculate Age Click the button "Search This Group" Read the messages that come up until you find one that gives information that meets your needs. You may find out how to calculate age. You may find out how to get answers to many questions, not just from those who might read your question today, but from years of reponses to similar questions. Jan 22 '06 #4

 P: n/a "DP" wrote in message news:D6*****************@newsfe6-gui.ntli.net... hi, i've got a customer table, with a date of birth field in it. its entered like; 12/12/1992 how would i generate an age through this field. e.g. i've got txtAge. how would i have the correct age inserted in there? thanx dev Dev, a quick clarification here. Several others have offered ideas how to calculate age. Do not store the age in a table! When you need to show the age in a form or report, use a query to compute the current age. It will always be based on the difference between DOB and the current Date. -- Randy Harris tech at promail dot com I'm pretty sure I know everything that I can remember. Jan 22 '06 #5

 P: n/a I think for speed here I'd make a Table of the first, say, 50000 dates after 1880. Then I'd create a one way self joining linking table mapping the table to itself, (1 250 025 000 records) and add three fields: years, months, days. Then I'd use whatever function I wanted for age and fill that table with the appropriate age value, eg, the first record in the table would show (1,1,0,0,0) ... (1,2,0,0,1). Then, of course, I do my query with a JOIN on the two fields. Jan 23 '06 #6

 P: n/a hi, i tried this, and the field sis just blank!. dev "sara" wrote in message news:11**********************@f14g2000cwb.googlegr oups.com... I would try =DateDiff("d",[txtBirth],Now())/365 as the control source in field txtAge. This assumes the DOB field is called "txtBirth" on the form. This calculates age from TODAY's date, using 365 days per year. Sara Jan 23 '06 #7

 P: n/a i'v done it now. thanx dev "DP" wrote in message news:xP****************@newsfe3-win.ntli.net... hi, i tried this, and the field sis just blank!. dev "sara" wrote in message news:11**********************@f14g2000cwb.googlegr oups.com... I would try =DateDiff("d",[txtBirth],Now())/365 as the control source in field txtAge. This assumes the DOB field is called "txtBirth" on the form. This calculates age from TODAY's date, using 365 days per year. Sara Jan 23 '06 #8

### This discussion thread is closed

Replies have been disabled for this discussion.