Expression for calculating age

[Aaron]

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??

 

[Glen SIdelnikov]

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

 

[Tony Williams]

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

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

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??

 

[Aaron]

That did it...thanks.

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

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??

.

 

[Allen]

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.

 

[Aaron]

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

That did it...thanks.

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


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??


.

.

 

[Joan Wild]

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.

 

[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

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

.

 

[Joan Wild]

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])

 

[Aaron]

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-----

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


.

 

[Joan Wild]

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?

No, it shouldn't.