Calculating Age

J

Julie Nicole

If I have the Date of Birth in a field, what expresion can I use in another
field, say AGE field, that would calculate the actual age automatically?

Like DOB field has 29/06/66 in it so Age field becomes 38 calculated
automatically when DOB is entered.

thanks
Julie
 
M

Mark

In the field you want to calculate the Age, bring up the
field properties, select the data tab and in the Control
Source enter

=DateDiff("yyyy",[DOB],Now())

Where [DOB] is your date of birth field
 
J

Julie Nicole

Great!!! Thanks Mark,

Is it possible to take it one step further where if say the DOB i13-06-03
that the forula in the age field would bring up 1/1 for 1 year and 1 month.
I didnt realise it had to be exact to the year and month. Its for show cats
and its inmortant that its calculated to the month as this depends on what
class they enter depending on their aage by year and month.

thanks
Julie


Mark said:
In the field you want to calculate the Age, bring up the
field properties, select the data tab and in the Control
Source enter

=DateDiff("yyyy",[DOB],Now())

Where [DOB] is your date of birth field

-----Original Message-----
If I have the Date of Birth in a field, what expresion can I use in another
field, say AGE field, that would calculate the actual age automatically?

Like DOB field has 29/06/66 in it so Age field becomes 38 calculated
automatically when DOB is entered.

thanks
Julie


.
 
J

Julie Nicole

Hi Mark,

Me again.
I send the last post before I forgot to ask one thing.
I have tried to figure this out myself and only resort to the newsgroup if I
am completely stuck. Would you mind explaining to be how that code works
and also, where I can read up on learning about this coding?
Would save me having to ask others questions all the time which I really
appreciate but some might think Im a nuisance. I have only just started
this database and its going to get tougher, but Im up for the challenge,
just would like to learn more about these VBA codes everyone is using. I
used to use Access 95 which was SO different. I didnt have to rely so much
on VBA.

thanks for your help!
regards
Julie

Mark said:
In the field you want to calculate the Age, bring up the
field properties, select the data tab and in the Control
Source enter

=DateDiff("yyyy",[DOB],Now())

Where [DOB] is your date of birth field

-----Original Message-----
If I have the Date of Birth in a field, what expresion can I use in another
field, say AGE field, that would calculate the actual age automatically?

Like DOB field has 29/06/66 in it so Age field becomes 38 calculated
automatically when DOB is entered.

thanks
Julie


.
 
M

Mark

This is quite a crude way of calculating an age. If you
select the Access Help menu and type Datediff there may be
an example and description of how to use it. It's not
strictly code but a built-in function of Access.

Just a word of warning - The DateDiff function is best
used to show the number of days between two dates. I
didn't realise you were using it for such a specific task.
If you enter a date of birth of 01/10/2003 you will see
the answer is 1 year (obviously not correct) because the
calculation uses a type of rounding.

To calculate the number of months, change the formula to

=DateDiff("m",[DOB],Now())


To calculate the number of days, change it to

=DateDiff("y",[DOB],Now())


It may be better to work with months rather than years for
your requirements.

Hope this helps



-----Original Message-----
Great!!! Thanks Mark,

Is it possible to take it one step further where if say the DOB i13-06-03
that the forula in the age field would bring up 1/1 for 1 year and 1 month.
I didnt realise it had to be exact to the year and month. Its for show cats
and its inmortant that its calculated to the month as this depends on what
class they enter depending on their aage by year and month.

thanks
Julie


In the field you want to calculate the Age, bring up the
field properties, select the data tab and in the Control
Source enter

=DateDiff("yyyy",[DOB],Now())

Where [DOB] is your date of birth field

-----Original Message-----
If I have the Date of Birth in a field, what expresion can I use in another
field, say AGE field, that would calculate the actual
age
automatically?
Like DOB field has 29/06/66 in it so Age field becomes
38
calculated
automatically when DOB is entered.

thanks
Julie


.


.
 
G

Graham R Seach

When trying to calculate anniversaries, DateDiff is prone to errors, simply
because of the way it works. Unless you take into account the way DateDiff
works, you can end up being a year off.

Perhaps the following functions can help:
http://www.pacificdb.com.au/MVP/Code/Age1.htm
http://www.pacificdb.com.au/MVP/Code/Age2.htm
http://www.pacificdb.com.au/MVP/Code/Diff2Dates.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Mark said:
This is quite a crude way of calculating an age. If you
select the Access Help menu and type Datediff there may be
an example and description of how to use it. It's not
strictly code but a built-in function of Access.

Just a word of warning - The DateDiff function is best
used to show the number of days between two dates. I
didn't realise you were using it for such a specific task.
If you enter a date of birth of 01/10/2003 you will see
the answer is 1 year (obviously not correct) because the
calculation uses a type of rounding.

To calculate the number of months, change the formula to

=DateDiff("m",[DOB],Now())


To calculate the number of days, change it to

=DateDiff("y",[DOB],Now())


It may be better to work with months rather than years for
your requirements.

Hope this helps



-----Original Message-----
Great!!! Thanks Mark,

Is it possible to take it one step further where if say the DOB i13-06-03
that the forula in the age field would bring up 1/1 for 1 year and 1 month.
I didnt realise it had to be exact to the year and month. Its for show cats
and its inmortant that its calculated to the month as this depends on what
class they enter depending on their aage by year and month.

thanks
Julie


In the field you want to calculate the Age, bring up the
field properties, select the data tab and in the Control
Source enter

=DateDiff("yyyy",[DOB],Now())

Where [DOB] is your date of birth field


-----Original Message-----
If I have the Date of Birth in a field, what expresion
can I use in another
field, say AGE field, that would calculate the actual age
automatically?

Like DOB field has 29/06/66 in it so Age field becomes 38
calculated
automatically when DOB is entered.

thanks
Julie


.


.
 
J

Julie Nicole

Hi Graham,

I have looked at the coding on one of the pages you gave me and find one
that is perfect but I dont know where to put the code.

What fields do I need in a table or query that are shown in the code?
I have added in extra fields and changed the dob field so I have all the
ones listed below vdate1 (which is dob), vdate2 (which will be the show
date), vyears (blank), vmonths(blank),vdays(blank).
I know to use the code builder and I copied it into that but it doesnt work.
Im not sure exactly where to put it or what fields I need.

Ive pasted the code below.
Would you mind giving me step by step instructions, Im only new to this.

regards
Julie



Public Sub CalcAge(vDate1 As Date, vdate2 As Date, ByRef vYears As Integer,
ByRef vMonths As Integer, ByRef vDays As Integer)
' Comments : calculates the age in Years, Months and Days
' Parameters:
' vDate1 - D.O.B.
' vDate2 - Date to calculate age based on
' vYears - will hold the Years difference
' vMonths - will hold the Months difference
' vDays - will hold the Days difference
vMonths = DateDiff("m", vDate1, vdate2)
vDays = DateDiff("d", DateAdd("m", vMonths, vDate1), vdate2)
If vDays < 0 Then
' wierd way that DateDiff works, fix it here
vMonths = vMonths - 1
vDays = DateDiff("d", DateAdd("m", vMonths, vDate1), vdate2)
End If
vYears = vMonths \ 12 ' integer division
vMonths = vMonths Mod 12 ' only want leftover less than one year
End Sub



Graham R Seach said:
When trying to calculate anniversaries, DateDiff is prone to errors, simply
because of the way it works. Unless you take into account the way DateDiff
works, you can end up being a year off.

Perhaps the following functions can help:
http://www.pacificdb.com.au/MVP/Code/Age1.htm
http://www.pacificdb.com.au/MVP/Code/Age2.htm
http://www.pacificdb.com.au/MVP/Code/Diff2Dates.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Mark said:
This is quite a crude way of calculating an age. If you
select the Access Help menu and type Datediff there may be
an example and description of how to use it. It's not
strictly code but a built-in function of Access.

Just a word of warning - The DateDiff function is best
used to show the number of days between two dates. I
didn't realise you were using it for such a specific task.
If you enter a date of birth of 01/10/2003 you will see
the answer is 1 year (obviously not correct) because the
calculation uses a type of rounding.

To calculate the number of months, change the formula to

=DateDiff("m",[DOB],Now())


To calculate the number of days, change it to

=DateDiff("y",[DOB],Now())


It may be better to work with months rather than years for
your requirements.

Hope this helps



-----Original Message-----
Great!!! Thanks Mark,

Is it possible to take it one step further where if say the DOB i13-06-03
that the forula in the age field would bring up 1/1 for 1 year and 1 month.
I didnt realise it had to be exact to the year and month. Its for show cats
and its inmortant that its calculated to the month as this depends on what
class they enter depending on their aage by year and month.

thanks
Julie


In the field you want to calculate the Age, bring up the
field properties, select the data tab and in the Control
Source enter

=DateDiff("yyyy",[DOB],Now())

Where [DOB] is your date of birth field


-----Original Message-----
If I have the Date of Birth in a field, what expresion
can I use in another
field, say AGE field, that would calculate the actual age
automatically?

Like DOB field has 29/06/66 in it so Age field becomes 38
calculated
automatically when DOB is entered.

thanks
Julie


.



.
 
J

Julie Nicole

Thanks but how do I use the code. I am only new to this and having a code
is one thing but where to put it is another! :)

I have a field with the DOB and a field with the show date both set to type
date. Do I need another field on the form that the code puts its answer in?
and if so what do I call this field so the code knows it?

thanks
Julie
 
W

Wayne Morgan

Yes, you're on the right track. First, a field is an item in table, a
control is an item on a form, such as a textbox. It frequently displays the
value of a field from a table.

The code would be placed in a module as a Public Function. Go to the modules
tab in the database window and create a new module. Place the Age code there
and save the module. Don't give the module the name of anything else,
especially not the name of any of the procedures in the module. The default
of Module1 will work, but I like to give things descriptive names when
possible.

Next, on your form, create an unbound textbox. For its control source, you
would insert and equation that calls the Age function and passes the value
of the DOB.

Example Control Source:
=Age([DOB])

A Public function is called and works just as the built in VBA functions do
(such as Left("text", 2), Date(), Round(number), etc).
 
J

Julie Nicole

You know Ive been up all night on this and it would only take you a minute!
Really appreciate your help, thanks for the encouragement aswell.

Ok, pasting the code into the module is easy :)

BUT, in the code it uses vdate1as D.O.B and vdate2 as other date which is
the show date. (what we need is the actual age of the cat by year and month
to make sure it enters the right ring on that show date)

So, because Ive used DOB and SDate. I changed the vdate1 to DOB and vdate2
to SDate to they refer to my fields, is this correct? Or is that totally
wrong ?)

Placing the control source is easy :) LOL
But it comes up blank and when I put it in again, close and reopen, its gone
again!

Was that an example or the actual control source?
There is no Age in the list under where the DateDiff thing is when you look
up functions.
Also, does the "control" :) have to have a format of either date or number,
and a name?
Im nearly there...I think!

thanks
Julie



Wayne Morgan said:
Yes, you're on the right track. First, a field is an item in table, a
control is an item on a form, such as a textbox. It frequently displays the
value of a field from a table.

The code would be placed in a module as a Public Function. Go to the modules
tab in the database window and create a new module. Place the Age code there
and save the module. Don't give the module the name of anything else,
especially not the name of any of the procedures in the module. The default
of Module1 will work, but I like to give things descriptive names when
possible.

Next, on your form, create an unbound textbox. For its control source, you
would insert and equation that calls the Age function and passes the value
of the DOB.

Example Control Source:
=Age([DOB])

A Public function is called and works just as the built in VBA functions do
(such as Left("text", 2), Date(), Round(number), etc).

--
Wayne Morgan
MS Access MVP


Julie Nicole said:
Thanks but how do I use the code. I am only new to this and having a code
is one thing but where to put it is another! :)

I have a field with the DOB and a field with the show date both set to type
date. Do I need another field on the form that the code puts its answer in?
and if so what do I call this field so the code knows it?
 
W

Wayne Morgan

Ok, you're using the third example, which is a Sub not a Function. It relies on the values being passed "by reference" (which is the default for VBA). The sub doesn't return a value. What it will do is modify the variables that you've passed to it. It wouldn't be good as a control source item, but does calculate the values you want, so let's do some modifying. Also, don't change the names of the variables in the Sub/Function. If they line up, left to right, with what you pass, that's all you need. The names are used internally in the sub/function and if you change them in the title line, you'll have to change them all the way through the sub/function.

For the modification, I will change the sub to a function and have it return a text string showing years and months.

'--- CODE START ---
'Public Sub CalcAge(vDate1 As Date, vdate2 As Date, ByRef vYears As Integer,ByRef vMonths As Integer, ByRef vDays As Integer)

Public Function CalcAge2(vDate1 As Date, Optional vDate2 As Variant) As String
' Comments : calculates the age in Years, Months and Days
' Parameters:
' vDate1 - D.O.B.
' vDate2 - Date to calculate age based on
' vYears - will hold the Years difference
' vMonths - will hold the Months difference
' vDays - will hold the Days difference
Dim vYears As Integer, vMonths As Integer, vDays As Integer
If IsMissing(vDate2) Then vDate2 = Date
vMonths = DateDiff("m", vDate1, vDate2)
vDays = DateDiff("d", DateAdd("m", vMonths, vDate1), vDate2)
If vDays < 0 Then
' wierd way that DateDiff works, fix it here
vMonths = vMonths - 1
vDays = DateDiff("d", DateAdd("m", vMonths, vDate1), vDate2)
End If
vYears = vMonths \ 12 ' integer division
vMonths = vMonths Mod 12 ' only want leftover less than one year
CalcAge2 = vYears & " Years, " & vMonths & " Months"
End Function
'--- CODE END ---

By making the 2nd date Optional, you can skip the second date and it will assume the current date. If you are doing this for a show on a future date, you could pass the date of the show here and the result would be for that date.

To call this function, use this control source:
=CalcAge2([DOB], [ShowDate])
or to use the current date automatically:
=CalcAge2([DOB])

--
Wayne Morgan
MS Access MVP


Julie Nicole said:
You know Ive been up all night on this and it would only take you a minute!
Really appreciate your help, thanks for the encouragement aswell.

Ok, pasting the code into the module is easy :)

BUT, in the code it uses vdate1as D.O.B and vdate2 as other date which is
the show date. (what we need is the actual age of the cat by year and month
to make sure it enters the right ring on that show date)

So, because Ive used DOB and SDate. I changed the vdate1 to DOB and vdate2
to SDate to they refer to my fields, is this correct? Or is that totally
wrong ?)

Placing the control source is easy :) LOL
But it comes up blank and when I put it in again, close and reopen, its gone
again!

Was that an example or the actual control source?
There is no Age in the list under where the DateDiff thing is when you look
up functions.
Also, does the "control" :) have to have a format of either date or number,
and a name?
Im nearly there...I think!

thanks
Julie



Wayne Morgan said:
Yes, you're on the right track. First, a field is an item in table, a
control is an item on a form, such as a textbox. It frequently displays the
value of a field from a table.

The code would be placed in a module as a Public Function. Go to the modules
tab in the database window and create a new module. Place the Age code there
and save the module. Don't give the module the name of anything else,
especially not the name of any of the procedures in the module. The default
of Module1 will work, but I like to give things descriptive names when
possible.

Next, on your form, create an unbound textbox. For its control source, you
would insert and equation that calls the Age function and passes the value
of the DOB.

Example Control Source:
=Age([DOB])

A Public function is called and works just as the built in VBA functions do
(such as Left("text", 2), Date(), Round(number), etc).

--
Wayne Morgan
MS Access MVP


Julie Nicole said:
Thanks but how do I use the code. I am only new to this and having a code
is one thing but where to put it is another! :)

I have a field with the DOB and a field with the show date both set to type
date. Do I need another field on the form that the code puts its answer in?
and if so what do I call this field so the code knows it?
 
G

Graham R Seach

Julie,

I see Wayne has perfectly answered your question, so there is no need for me
to elaborate.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html



Julie Nicole said:
Hi Graham,

I have looked at the coding on one of the pages you gave me and find one
that is perfect but I dont know where to put the code.

What fields do I need in a table or query that are shown in the code?
I have added in extra fields and changed the dob field so I have all the
ones listed below vdate1 (which is dob), vdate2 (which will be the show
date), vyears (blank), vmonths(blank),vdays(blank).
I know to use the code builder and I copied it into that but it doesnt work.
Im not sure exactly where to put it or what fields I need.

Ive pasted the code below.
Would you mind giving me step by step instructions, Im only new to this.

regards
Julie



Public Sub CalcAge(vDate1 As Date, vdate2 As Date, ByRef vYears As Integer,
ByRef vMonths As Integer, ByRef vDays As Integer)
' Comments : calculates the age in Years, Months and Days
' Parameters:
' vDate1 - D.O.B.
' vDate2 - Date to calculate age based on
' vYears - will hold the Years difference
' vMonths - will hold the Months difference
' vDays - will hold the Days difference
vMonths = DateDiff("m", vDate1, vdate2)
vDays = DateDiff("d", DateAdd("m", vMonths, vDate1), vdate2)
If vDays < 0 Then
' wierd way that DateDiff works, fix it here
vMonths = vMonths - 1
vDays = DateDiff("d", DateAdd("m", vMonths, vDate1), vdate2)
End If
vYears = vMonths \ 12 ' integer division
vMonths = vMonths Mod 12 ' only want leftover less than one year
End Sub



Graham R Seach said:
When trying to calculate anniversaries, DateDiff is prone to errors, simply
because of the way it works. Unless you take into account the way DateDiff
works, you can end up being a year off.

Perhaps the following functions can help:
http://www.pacificdb.com.au/MVP/Code/Age1.htm
http://www.pacificdb.com.au/MVP/Code/Age2.htm
http://www.pacificdb.com.au/MVP/Code/Diff2Dates.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Mark said:
This is quite a crude way of calculating an age. If you
select the Access Help menu and type Datediff there may be
an example and description of how to use it. It's not
strictly code but a built-in function of Access.

Just a word of warning - The DateDiff function is best
used to show the number of days between two dates. I
didn't realise you were using it for such a specific task.
If you enter a date of birth of 01/10/2003 you will see
the answer is 1 year (obviously not correct) because the
calculation uses a type of rounding.

To calculate the number of months, change the formula to

=DateDiff("m",[DOB],Now())


To calculate the number of days, change it to

=DateDiff("y",[DOB],Now())


It may be better to work with months rather than years for
your requirements.

Hope this helps




-----Original Message-----
Great!!! Thanks Mark,

Is it possible to take it one step further where if say
the DOB i13-06-03
that the forula in the age field would bring up 1/1 for 1
year and 1 month.
I didnt realise it had to be exact to the year and
month. Its for show cats
and its inmortant that its calculated to the month as
this depends on what
class they enter depending on their aage by year and
month.

thanks
Julie


message
In the field you want to calculate the Age, bring up the
field properties, select the data tab and in the Control
Source enter

=DateDiff("yyyy",[DOB],Now())

Where [DOB] is your date of birth field


-----Original Message-----
If I have the Date of Birth in a field, what expresion
can I use in another
field, say AGE field, that would calculate the actual
age
automatically?

Like DOB field has 29/06/66 in it so Age field becomes
38
calculated
automatically when DOB is entered.

thanks
Julie


.



.
 
J

Julie Nicole

Hi Wayne,

Sorry its taken me so long to get back to you. I havent been well the past couple of weeks and just couldnt get on the pc. Today having felt better than I have for a while, was itching to just get back on and give this another go with your reply below giving such helpful detail, IT WORKS!!!!!!

Thank you so much for all your help.
I really appreciate it.
regards
Julie
Ok, you're using the third example, which is a Sub not a Function. It relies on the values being passed "by reference" (which is the default for VBA). The sub doesn't return a value. What it will do is modify the variables that you've passed to it. It wouldn't be good as a control source item, but does calculate the values you want, so let's do some modifying. Also, don't change the names of the variables in the Sub/Function. If they line up, left to right, with what you pass, that's all you need. The names are used internally in the sub/function and if you change them in the title line, you'll have to change them all the way through the sub/function.

For the modification, I will change the sub to a function and have it return a text string showing years and months.

'--- CODE START ---
'Public Sub CalcAge(vDate1 As Date, vdate2 As Date, ByRef vYears As Integer,ByRef vMonths As Integer, ByRef vDays As Integer)

Public Function CalcAge2(vDate1 As Date, Optional vDate2 As Variant) As String
' Comments : calculates the age in Years, Months and Days
' Parameters:
' vDate1 - D.O.B.
' vDate2 - Date to calculate age based on
' vYears - will hold the Years difference
' vMonths - will hold the Months difference
' vDays - will hold the Days difference
Dim vYears As Integer, vMonths As Integer, vDays As Integer
If IsMissing(vDate2) Then vDate2 = Date
vMonths = DateDiff("m", vDate1, vDate2)
vDays = DateDiff("d", DateAdd("m", vMonths, vDate1), vDate2)
If vDays < 0 Then
' wierd way that DateDiff works, fix it here
vMonths = vMonths - 1
vDays = DateDiff("d", DateAdd("m", vMonths, vDate1), vDate2)
End If
vYears = vMonths \ 12 ' integer division
vMonths = vMonths Mod 12 ' only want leftover less than one year
CalcAge2 = vYears & " Years, " & vMonths & " Months"
End Function
'--- CODE END ---

By making the 2nd date Optional, you can skip the second date and it will assume the current date. If you are doing this for a show on a future date, you could pass the date of the show here and the result would be for that date.

To call this function, use this control source:
=CalcAge2([DOB], [ShowDate])
or to use the current date automatically:
=CalcAge2([DOB])

--
Wayne Morgan
MS Access MVP


Julie Nicole said:
You know Ive been up all night on this and it would only take you a minute!
Really appreciate your help, thanks for the encouragement aswell.

Ok, pasting the code into the module is easy :)

BUT, in the code it uses vdate1as D.O.B and vdate2 as other date which is
the show date. (what we need is the actual age of the cat by year and month
to make sure it enters the right ring on that show date)

So, because Ive used DOB and SDate. I changed the vdate1 to DOB and vdate2
to SDate to they refer to my fields, is this correct? Or is that totally
wrong ?)

Placing the control source is easy :) LOL
But it comes up blank and when I put it in again, close and reopen, its gone
again!

Was that an example or the actual control source?
There is no Age in the list under where the DateDiff thing is when you look
up functions.
Also, does the "control" :) have to have a format of either date or number,
and a name?
Im nearly there...I think!

thanks
Julie



Wayne Morgan said:
Yes, you're on the right track. First, a field is an item in table, a
control is an item on a form, such as a textbox. It frequently displays the
value of a field from a table.

The code would be placed in a module as a Public Function. Go to the modules
tab in the database window and create a new module. Place the Age code there
and save the module. Don't give the module the name of anything else,
especially not the name of any of the procedures in the module. The default
of Module1 will work, but I like to give things descriptive names when
possible.

Next, on your form, create an unbound textbox. For its control source, you
would insert and equation that calls the Age function and passes the value
of the DOB.

Example Control Source:
=Age([DOB])

A Public function is called and works just as the built in VBA functions do
(such as Left("text", 2), Date(), Round(number), etc).

--
Wayne Morgan
MS Access MVP


Julie Nicole said:
Thanks but how do I use the code. I am only new to this and having a code
is one thing but where to put it is another! :)

I have a field with the DOB and a field with the show date both set to type
date. Do I need another field on the form that the code puts its answer in?
and if so what do I call this field so the code knows it?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top