I'm making a simple conact db in which I want a field to
automatically calculate the age of a contact using the
current date and their birthdate. I was running into
trouble b/c of format issues. Is there a simple way to go
about this??

What about Year(Now) - Year([birthdate])?


"Aaron" <(E-Mail Removed)> wrote in message
news:0cf701c35779$677ed310$(E-Mail Removed)...
> I'm making a simple conact db in which I want a field to
> automatically calculate the age of a contact using the
> current date and their birthdate. I was running into
> trouble b/c of format issues. Is there a simple way to go
> about this??

Hi Aaron this worked for me
I have a field Date of Birth and I created a textbox control called txtAge
and on its On Enter event put this code
Private Sub txtAge_Enter()
Dim dateBirthday As Date
dateBirthday = Date_of_Birth.Value
Dim varAge As Variant
varAge = -DateDiff("yyyy", Date, dateBirthday)
Dim varDiff As Variant
varDiff = DateDiff("d", DateAdd("yyyy", varAge, dateBirthday), Date)
If varDiff < 0 Then
varAge = varAge - 1
End If
txtAge.Value = varAge
If txtAge.Value < 18 Then
MsgBox "This person is under 18!", vbOKOnly, "Warning"
End If
End Sub

Hope that helped
Tony Williams

"Glen SIdelnikov" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> What about Year(Now) - Year([birthdate])?
>
>
> "Aaron" <(E-Mail Removed)> wrote in message
> news:0cf701c35779$677ed310$(E-Mail Removed)...
> > I'm making a simple conact db in which I want a field to
> > automatically calculate the age of a contact using the
> > current date and their birthdate. I was running into
> > trouble b/c of format issues. Is there a simple way to go
> > about this??
>
>

That did it...thanks.
>-----Original Message-----
>What about Year(Now) - Year([birthdate])?
>
>
>"Aaron" <(E-Mail Removed)> wrote in message
>news:0cf701c35779$677ed310$(E-Mail Removed)...
>> I'm making a simple conact db in which I want a field to
>> automatically calculate the age of a contact using the
>> current date and their birthdate. I was running into
>> trouble b/c of format issues. Is there a simple way to
go
>> about this??
>
>
>.
>

I use this expression in a query: Age =(Now()-[Date of
Birth])/365. Then in properties set format to standard.
I usually give it 2 decimal places.

>-----Original Message-----
>I'm making a simple conact db in which I want a field to
>automatically calculate the age of a contact using the
>current date and their birthdate. I was running into
>trouble b/c of format issues. Is there a simple way to
go
>about this??
>.
>

You are right. there is a big hole in the previous
suggestions. I looked at the website and copied the
function, but there's an error in the db now. I'm not an
expert on Access or db language commands - somewhat
familiar and it intuitively makes sense - but still
limited. Do I need to create different fields than simply
a short date field (i.e. one for day, one for month) or
introduce the new field names in the existing structure?

Aaron
>-----Original Message-----
>If you interested in a function that is accurate (and a
reason why every one
>I see in this thread is not) see
> http://users.bigpond.net.au/abrowne1/func-08.html
>
>--
>Joan Wild
>Microsoft Access MVP
>
>"Aaron" <(E-Mail Removed)> wrote in message
>news:0ebd01c35780$3c494110$(E-Mail Removed)...
>> That did it...thanks.
>> >-----Original Message-----
>> >What about Year(Now) - Year([birthdate])?
>> >
>> >
>> >"Aaron" <(E-Mail Removed)> wrote in message
>> >news:0cf701c35779$677ed310$(E-Mail Removed)...
>> >> I'm making a simple conact db in which I want a
field to
>> >> automatically calculate the age of a contact using
the
>> >> current date and their birthdate. I was running into
>> >> trouble b/c of format issues. Is there a simple way
to
>> go
>> >> about this??
>> >
>> >
>> >.
>> >
>
>
>.
>

I assume you have a field in your table with the person's date of birth.
This would be a date/time field type.

You would not actually store their age in the table. Instead you can use
the function in a query or as a control source of a textbox on a form or
report.

Paste the function in a module in your database. Ensure that you don't give
the module the same name as the function.

Create a query based on your table. In an empty column of the grid, you
would put

Age([Your DOB field])

When you run the query, that column will show the person's age as of today.


--
Joan Wild
Microsoft Access MVP

"Aaron" <(E-Mail Removed)> wrote in message
news:0b6301c3583a$77080e70$(E-Mail Removed)...
> You are right. there is a big hole in the previous
> suggestions. I looked at the website and copied the
> function, but there's an error in the db now. I'm not an
> expert on Access or db language commands - somewhat
> familiar and it intuitively makes sense - but still
> limited. Do I need to create different fields than simply
> a short date field (i.e. one for day, one for month) or
> introduce the new field names in the existing structure?
>
> Aaron

ok..I gotcha. Originally, I just copied the function into
the control source field of the textbox (age). it didn't
seem to work because I got the #Name? error. That's when
I thought it was needing to know what the controls were
referenced in the function. Am I on the right track?
Also, I will try the other route of creating the module,
then a query and have the control source ot the textbox
(age) point to the query. Would that work as well?

Aaron
>-----Original Message-----
>I assume you have a field in your table with the person's
date of birth.
>This would be a date/time field type.
>
>You would not actually store their age in the table.
Instead you can use
>the function in a query or as a control source of a
textbox on a form or
>report.
>
>Paste the function in a module in your database. Ensure
that you don't give
>the module the same name as the function.
>
>Create a query based on your table. In an empty column
of the grid, you
>would put
>
>Age([Your DOB field])
>
>When you run the query, that column will show the
person's age as of today.
>
>
>--
>Joan Wild
>Microsoft Access MVP
>
>"Aaron" <(E-Mail Removed)> wrote in message
>news:0b6301c3583a$77080e70$(E-Mail Removed)...
>> You are right. there is a big hole in the previous
>> suggestions. I looked at the website and copied the
>> function, but there's an error in the db now. I'm not
an
>> expert on Access or db language commands - somewhat
>> familiar and it intuitively makes sense - but still
>> limited. Do I need to create different fields than
simply
>> a short date field (i.e. one for day, one for month) or
>> introduce the new field names in the existing structure?
>>
>> Aaron
>
>
>.
>

"Aaron" <(E-Mail Removed)> wrote in message
news:6ca701c35866$d745cc10$(E-Mail Removed)...
> ok..I gotcha. Originally, I just copied the function into
> the control source field of the textbox (age). it didn't
> seem to work because I got the #Name? error. That's when
> I thought it was needing to know what the controls were
> referenced in the function. Am I on the right track?
> Also, I will try the other route of creating the module,
> then a query and have the control source ot the textbox
> (age) point to the query. Would that work as well?
>
> Aaron

You have to create the module, no matter what route you take.

You then can use the function in a number of ways.

1. As a calculated column in a query, as described before (and yes then a
textbox on a form or a report could be bound to this column)

2. If it's not in the query (but your DOB field is), then on a form or
report, you can add a textbox, and set it's control source to
=Age([DOB Field])

--
Joan Wild
Microsoft Access MVP

Success! thanks for the help. I opted to have the text
box point to the query which contains the Birthdate
field. Now, if I use the same query to build a report,
and add columns to the query, it shouldn't mess up the age
operation should it?

>-----Original Message-----
>
>"Aaron" <(E-Mail Removed)> wrote in message
>news:6ca701c35866$d745cc10$(E-Mail Removed)...
>> ok..I gotcha. Originally, I just copied the function
into
>> the control source field of the textbox (age). it
didn't
>> seem to work because I got the #Name? error. That's
when
>> I thought it was needing to know what the controls were
>> referenced in the function. Am I on the right track?
>> Also, I will try the other route of creating the module,
>> then a query and have the control source ot the textbox
>> (age) point to the query. Would that work as well?
>>
>> Aaron
>
>You have to create the module, no matter what route you
take.
>
>You then can use the function in a number of ways.
>
>1. As a calculated column in a query, as described before
(and yes then a
>textbox on a form or a report could be bound to this
column)
>
>2. If it's not in the query (but your DOB field is), then
on a form or
>report, you can add a textbox, and set it's control
source to
>=Age([DOB Field])
>
>--
>Joan Wild
>Microsoft Access MVP
>
>
>.
>