generate age

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

re: generate age

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

re: generate age

"DP" <DP@hotmail.com> wrote in message
news:D6QAf.12146$Kt5.128@newsfe6-gui.ntli.net...[color=blue]
> 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[/color]



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

re: generate age

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.

re: generate age

"DP" <DP@hotmail.com> wrote in message
news:D6QAf.12146$Kt5.128@newsfe6-gui.ntli.net...[color=blue]
> 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[/color]


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.

re: generate age

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.

re: generate age

hi,

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

dev

"sara" <saraqpost@yahoo.com> wrote in message
news:1137959812.388582.239900@f14g2000cwb.googlegr oups.com...[color=blue]
> 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
>[/color]

re: generate age

i'v done it now.

thanx

dev

"DP" <DP@hotmail.com> wrote in message
news:xP1Bf.6715$Y6.5190@newsfe3-win.ntli.net...[color=blue]
> hi,
>
> i tried this, and the field sis just blank!.
>
> dev
>
> "sara" <saraqpost@yahoo.com> wrote in message
> news:1137959812.388582.239900@f14g2000cwb.googlegr oups.com...[color=green]
> > 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
> >[/color]
>
>[/color]