By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,390 Online
Bytes IT Community
+ Ask a Question
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
DP
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
Share this Question
Share on Google+
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" <DP@hotmail.com> 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" <DP@hotmail.com> 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
DP
hi,

i tried this, and the field sis just blank!.

dev

"sara" <sa*******@yahoo.com> 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
DP
i'v done it now.

thanx

dev

"DP" <DP@hotmail.com> wrote in message
news:xP****************@newsfe3-win.ntli.net...
hi,

i tried this, and the field sis just blank!.

dev

"sara" <sa*******@yahoo.com> 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.