Calculate age on a specific date

  • Thread starter Thread starter Bagheera
  • Start date Start date
B

Bagheera

I would like automatically to calculate the age of people on a specific date
(21st September 2008, as it happens) by typing in their date of birth.

I have tried the DateDiff function but this just appears to take the years
and people whose birthdays are after 21st September are aged by a year.

Help with this would be most gratefully received.

Thank you.
 
I don't think so because then those people who have already had their
birthdays before 21st September would be a year younger. They probably
wouldn't object but it would make my life difficult.
 
Thank you for this. Unfortunately, I'm afraid I don't know how to use this
information. I copied and pasted the code into the After Update event but got
an error.

Also, I couldn't see where to type in the specific date on which I want to
calculate the age. Is it the line:
dtAsOf = Date
so do I type my date in instead of 'Date'?

I'm afraid I'm really not too good with VB so idiot-proof instructions would
be really helpful if you've got the time, please.

Thank you.
 
1. Open the code window in Access.

2. On the Insert menu, choose Module.
Access opens a new code window.

3. Paste in everythin from Function ... to End Function.

4. To verify that Access understands it, choose Compile on the Debug menu.

5. Save the module with a name such as Module1.

You can now use the function anywhere. For example, of you have a field
named BirthDate, and want to know the person's age as of 21 Sep '08, put a
text box on your form and set its ControlSource to:
=Age([BirthDate], #09/21/08#)

(You should use the US format for the literal date, regardless of your
regional settings.)
 
Thank you for this. Unfortunately, I'm getting a run-time error 13 - Type
Mismatch.

I entered

[Age] = Age([DateOfBirth], "9/21/2008")

I tried it with hash signs either side of the fixed date as well as "".

Sorry to be so thick.




Allen Browne said:
1. Open the code window in Access.

2. On the Insert menu, choose Module.
Access opens a new code window.

3. Paste in everythin from Function ... to End Function.

4. To verify that Access understands it, choose Compile on the Debug menu.

5. Save the module with a name such as Module1.

You can now use the function anywhere. For example, of you have a field
named BirthDate, and want to know the person's age as of 21 Sep '08, put a
text box on your form and set its ControlSource to:
=Age([BirthDate], #09/21/08#)

(You should use the US format for the literal date, regardless of your
regional settings.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Bagheera said:
Thank you for this. Unfortunately, I'm afraid I don't know how to use this
information. I copied and pasted the code into the After Update event but
got
an error.

Also, I couldn't see where to type in the specific date on which I want to
calculate the age. Is it the line:
dtAsOf = Date
so do I type my date in instead of 'Date'?

I'm afraid I'm really not too good with VB so idiot-proof instructions
would
be really helpful if you've got the time, please.

Thank you.
Click to expand...
Click to expand...
 
Where did you type this?

The suggestion was to type it into the Control Source of a text box.
Do not include the [Age], i.e. the expression starts with =.

Also, you must use # around the date, not quotes.
See previous example.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Bagheera said:
Thank you for this. Unfortunately, I'm getting a run-time error 13 - Type
Mismatch.

I entered

[Age] = Age([DateOfBirth], "9/21/2008")

I tried it with hash signs either side of the fixed date as well as "".

Sorry to be so thick.




Allen Browne said:
1. Open the code window in Access.

2. On the Insert menu, choose Module.
Access opens a new code window.

3. Paste in everythin from Function ... to End Function.

4. To verify that Access understands it, choose Compile on the Debug
menu.

5. Save the module with a name such as Module1.

You can now use the function anywhere. For example, of you have a field
named BirthDate, and want to know the person's age as of 21 Sep '08, put
a
text box on your form and set its ControlSource to:
=Age([BirthDate], #09/21/08#)

(You should use the US format for the literal date, regardless of your
regional settings.)

Bagheera said:
Thank you for this. Unfortunately, I'm afraid I don't know how to use
this
information. I copied and pasted the code into the After Update event
but
got
an error.

Also, I couldn't see where to type in the specific date on which I want
to
calculate the age. Is it the line:
dtAsOf = Date
so do I type my date in instead of 'Date'?

I'm afraid I'm really not too good with VB so idiot-proof instructions
would
be really helpful if you've got the time, please.

:

See:
http://allenbrowne.com/func-08.html

The function takes 2 arguments:
- the birth date
- the date to calculate the age at.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I would like automatically to calculate the age of people on a
specific
date
(21st September 2008, as it happens) by typing in their date of
birth.

I have tried the DateDiff function but this just appears to take the
years
and people whose birthdays are after 21st September are aged by a
year.
Click to expand...
Click to expand...
Click to expand...
 
I put it in the After Update event of the DateOfBirth field. The age of the
people is something that I need to use in other areas and would like to be
able to save it rather than calculate it on each form and report on which is
appears. There is a field called 'Age' which is why I put it before the =
sign.


Allen Browne said:
Where did you type this?

The suggestion was to type it into the Control Source of a text box.
Do not include the [Age], i.e. the expression starts with =.

Also, you must use # around the date, not quotes.
See previous example.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Bagheera said:
Thank you for this. Unfortunately, I'm getting a run-time error 13 - Type
Mismatch.

I entered

[Age] = Age([DateOfBirth], "9/21/2008")

I tried it with hash signs either side of the fixed date as well as "".

Sorry to be so thick.




Allen Browne said:
1. Open the code window in Access.

2. On the Insert menu, choose Module.
Access opens a new code window.

3. Paste in everythin from Function ... to End Function.

4. To verify that Access understands it, choose Compile on the Debug
menu.

5. Save the module with a name such as Module1.

You can now use the function anywhere. For example, of you have a field
named BirthDate, and want to know the person's age as of 21 Sep '08, put
a
text box on your form and set its ControlSource to:
=Age([BirthDate], #09/21/08#)

(You should use the US format for the literal date, regardless of your
regional settings.)

Thank you for this. Unfortunately, I'm afraid I don't know how to use
this
information. I copied and pasted the code into the After Update event
but
got
an error.

Also, I couldn't see where to type in the specific date on which I want
to
calculate the age. Is it the line:
dtAsOf = Date
so do I type my date in instead of 'Date'?

I'm afraid I'm really not too good with VB so idiot-proof instructions
would
be really helpful if you've got the time, please.

:

See:
http://allenbrowne.com/func-08.html

The function takes 2 arguments:
- the birth date
- the date to calculate the age at.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I would like automatically to calculate the age of people on a
specific
date
(21st September 2008, as it happens) by typing in their date of
birth.

I have tried the DateDiff function but this just appears to take the
years
and people whose birthdays are after 21st September are aged by a
year.
Click to expand...
Click to expand...
Click to expand...
 
So using # rather than " probably solved the issue.

I could not imagine any case (other than a temporary table) where storing
the age could be valid. With only a couple of hundred people, the chances
are greater than 50% that you have bad data in the table every day.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Bagheera said:
I put it in the After Update event of the DateOfBirth field. The age of the
people is something that I need to use in other areas and would like to be
able to save it rather than calculate it on each form and report on which
is
appears. There is a field called 'Age' which is why I put it before the =
sign.


Allen Browne said:
Where did you type this?

The suggestion was to type it into the Control Source of a text box.
Do not include the [Age], i.e. the expression starts with =.

Also, you must use # around the date, not quotes.
See previous example.

Bagheera said:
Thank you for this. Unfortunately, I'm getting a run-time error 13 -
Type
Mismatch.

I entered

[Age] = Age([DateOfBirth], "9/21/2008")

I tried it with hash signs either side of the fixed date as well as "".

Sorry to be so thick.




:

1. Open the code window in Access.

2. On the Insert menu, choose Module.
Access opens a new code window.

3. Paste in everythin from Function ... to End Function.

4. To verify that Access understands it, choose Compile on the Debug
menu.

5. Save the module with a name such as Module1.

You can now use the function anywhere. For example, of you have a
field
named BirthDate, and want to know the person's age as of 21 Sep '08,
put
a
text box on your form and set its ControlSource to:
=Age([BirthDate], #09/21/08#)

(You should use the US format for the literal date, regardless of your
regional settings.)

Thank you for this. Unfortunately, I'm afraid I don't know how to
use
this
information. I copied and pasted the code into the After Update
event
but
got
an error.

Also, I couldn't see where to type in the specific date on which I
want
to
calculate the age. Is it the line:
dtAsOf = Date
so do I type my date in instead of 'Date'?

I'm afraid I'm really not too good with VB so idiot-proof
instructions
would
be really helpful if you've got the time, please.

:

See:
http://allenbrowne.com/func-08.html

The function takes 2 arguments:
- the birth date
- the date to calculate the age at.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I would like automatically to calculate the age of people on a
specific
date
(21st September 2008, as it happens) by typing in their date of
birth.

I have tried the DateDiff function but this just appears to take
the
years
and people whose birthdays are after 21st September are aged by a
year.
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your patience. Sadly, I still can't get this to work for me. I
replaced the " with # as instructed with the same error. I created a new
control with the calculation but get #Error in the field. Incidentally,
despite typing in the date in the formula in US format, it converts it to the
UK format.

With more than 1,000 records expected, it would have been nice to have been
able to get the age to calculate automatically - it's pretty much the crux of
a whole lot of other things for the database and crops up repeatedly but you
can't have everything.

Thanks for trying though.


Allen Browne said:
So using # rather than " probably solved the issue.

I could not imagine any case (other than a temporary table) where storing
the age could be valid. With only a couple of hundred people, the chances
are greater than 50% that you have bad data in the table every day.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Bagheera said:
I put it in the After Update event of the DateOfBirth field. The age of the
people is something that I need to use in other areas and would like to be
able to save it rather than calculate it on each form and report on which
is
appears. There is a field called 'Age' which is why I put it before the =
sign.


Allen Browne said:
Where did you type this?

The suggestion was to type it into the Control Source of a text box.
Do not include the [Age], i.e. the expression starts with =.

Also, you must use # around the date, not quotes.
See previous example.

Thank you for this. Unfortunately, I'm getting a run-time error 13 -
Type
Mismatch.

I entered

[Age] = Age([DateOfBirth], "9/21/2008")

I tried it with hash signs either side of the fixed date as well as "".

Sorry to be so thick.




:

1. Open the code window in Access.

2. On the Insert menu, choose Module.
Access opens a new code window.

3. Paste in everythin from Function ... to End Function.

4. To verify that Access understands it, choose Compile on the Debug
menu.

5. Save the module with a name such as Module1.

You can now use the function anywhere. For example, of you have a
field
named BirthDate, and want to know the person's age as of 21 Sep '08,
put
a
text box on your form and set its ControlSource to:
=Age([BirthDate], #09/21/08#)

(You should use the US format for the literal date, regardless of your
regional settings.)

Thank you for this. Unfortunately, I'm afraid I don't know how to
use
this
information. I copied and pasted the code into the After Update
event
but
got
an error.

Also, I couldn't see where to type in the specific date on which I
want
to
calculate the age. Is it the line:
dtAsOf = Date
so do I type my date in instead of 'Date'?

I'm afraid I'm really not too good with VB so idiot-proof
instructions
would
be really helpful if you've got the time, please.

:

See:
http://allenbrowne.com/func-08.html

The function takes 2 arguments:
- the birth date
- the date to calculate the age at.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I would like automatically to calculate the age of people on a
specific
date
(21st September 2008, as it happens) by typing in their date of
birth.

I have tried the DateDiff function but this just appears to take
the
years
and people whose birthdays are after 21st September are aged by a
year.
Click to expand...
Click to expand...
Click to expand...
 
To debug the function, open the Immediate Window (Ctrl+G) and enter
something like:
? Age(#1/1/1980#, #7/23/2008#)

Once you have that working, verify that your DateOfBirth field is actually a
date/time field (not text.)

Then add a breakpoint to your code, and test what's going on.

The function does work.
 
I'm not questioning your function, I'm questioning my ability to get it to
work for me. I copied and pasted the example below and still got an error.
Never mind, we'll just have to enter the data manually - or get Microsoft to
sort out the DateDiff function so that it yields the answer we need.

Thanks.
 
Back
Top