PC Review


Reply
Thread Tools Rate Thread

Expression for calculating age

 
 
Aaron
Guest
Posts: n/a
 
      31st Jul 2003
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??
 
Reply With Quote
 
 
 
 
Glen SIdelnikov
Guest
Posts: n/a
 
      31st Jul 2003
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??



 
Reply With Quote
 
 
 
 
Tony Williams
Guest
Posts: n/a
 
      31st Jul 2003
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??

>
>



 
Reply With Quote
 
Aaron
Guest
Posts: n/a
 
      31st Jul 2003
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??

>
>
>.
>

 
Reply With Quote
 
Allen
Guest
Posts: n/a
 
      31st Jul 2003
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??
>.
>

 
Reply With Quote
 
Aaron
Guest
Posts: n/a
 
      1st Aug 2003
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??
>> >
>> >
>> >.
>> >

>
>
>.
>

 
Reply With Quote
 
Joan Wild
Guest
Posts: n/a
 
      1st Aug 2003
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



 
Reply With Quote
 
Aaron
Guest
Posts: n/a
 
      1st Aug 2003
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

>
>
>.
>

 
Reply With Quote
 
Joan Wild
Guest
Posts: n/a
 
      1st Aug 2003

"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


 
Reply With Quote
 
Aaron
Guest
Posts: n/a
 
      8th Aug 2003
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
>
>
>.
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
CALCULATING WORKSHEETS (INCLUDING AGE CALCULATING SHEETS) FOR DOWNLOADING, GREAT FOR PENSIONS/LIFE INSURANCE CALCULATIONS! RICHARD Microsoft Excel Programming 0 15th Mar 2005 02:41 PM
Age of Mythology: You must have administrator rights to play Age of Mythology =?Utf-8?B?RWQgTWlsbHM=?= Windows XP Games 2 1st May 2004 09:11 AM
age of empires 2 the age of kings =?Utf-8?B?YnViYmE=?= Windows XP Games 1 13th Mar 2004 07:14 PM
Age of Empires II: Age of Kings Danny W Windows XP General 1 23rd Nov 2003 09:32 AM
Age Of Empires: The Age Of Kings Josh Windows XP Games 2 23rd Oct 2003 07:03 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 05:18 AM.