Age calculated from the medium format (4-Jan-94) and how to populated the table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to find a code for a table to automatically
calculate the age. I would like to do it in the table. I
found code for doing it in the form but from the short
date format. I cannot and do not want to change the date
format.

the code for the form is as follows: =DateDiff("yyyy",
[Birthdate,Now())+Int(Format(now(),"mmdd")<Format
([Birthdate],"mmdd"))

I tryed playing with it around but I cannot get it to work.

Thank you.
 
The formula should work as long as the [Birthdate] field is stored as a
date. Put a right bracket after the "[Birthdate".

It doesn't matter how the date is displayed as long as it's stored as a
date.
 
As long as BirthDate is a Date/Time field (not a Text field), the format of
the date makes no difference.

You must not store the age in a table field. With just 400 people in the
database, your data would be wrong every day if you store the age! For more
information on why not and how to achieve what you need, see:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

The expression you had will work in a query, or you can use a function like:
http://allenbrowne.com/func-08.html

Since you are working with a British date format, this article may also help
you avoid the 3 cases where Access can misunderstand your dates:
International Date Formats in Access
in:
http://allenbrowne.com/ser-36.html
 
Try this....

=(now()-[birthdate])/365.25.

Using 365.25 accounts for leap years.
Then format the result however you wish.

Kind regards
Deb Roberts

Joe Clarke said:
The formula should work as long as the [Birthdate] field is stored as a
date. Put a right bracket after the "[Birthdate".

It doesn't matter how the date is displayed as long as it's stored as a
date.


I would like to find a code for a table to automatically
calculate the age. I would like to do it in the table. I
found code for doing it in the form but from the short
date format. I cannot and do not want to change the date
format.

the code for the form is as follows: =DateDiff("yyyy",
[Birthdate,Now())+Int(Format(now(),"mmdd")<Format
([Birthdate],"mmdd"))

I tryed playing with it around but I cannot get it to work.

Thank you.
Click to expand...
Click to expand...
 
I tried it but I still getting this message:

InvalidSQL syntax-cannot use multiple columns in a column-
level CHECK constraint
-----Original Message-----
Try this....

=(now()-[birthdate])/365.25.

Using 365.25 accounts for leap years.
Then format the result however you wish.

Kind regards
Deb Roberts

Joe Clarke said:
The formula should work as long as the [Birthdate] field is stored as a
date. Put a right bracket after the "[Birthdate".

It doesn't matter how the date is displayed as long as it's stored as a
date.


I would like to find a code for a table to automatically
calculate the age. I would like to do it in the table. I
found code for doing it in the form but from the short
date format. I cannot and do not want to change the date
format.

the code for the form is as follows: =DateDiff("yyyy",
[Birthdate,Now())+Int(Format(now(),"mmdd")<Format
([Birthdate],"mmdd"))

I tryed playing with it around but I cannot get it to work.

Thank you.
Click to expand...
Click to expand...


.
Click to expand...
 
I tried it but I still getting this message:

InvalidSQL syntax-cannot use multiple columns in a column-
level CHECK constraint
-----Original Message-----
The formula should work as long as the [Birthdate] field is stored as a
date. Put a right bracket after the "[Birthdate".

It doesn't matter how the date is displayed as long as it's stored as a
date.


I would like to find a code for a table to automatically
calculate the age. I would like to do it in the table. I
found code for doing it in the form but from the short
date format. I cannot and do not want to change the date
format.

the code for the form is as follows: =DateDiff("yyyy",
[Birthdate,Now())+Int(Format(now(),"mmdd")<Format
([Birthdate],"mmdd"))

I tryed playing with it around but I cannot get it to work.

Thank you.
Click to expand...


.
Click to expand...
 
I tried it but I still getting this message:

InvalidSQL syntax-cannot use multiple columns in a column-
level CHECK constraint
Click to expand...

Please post your SQL. It appears that you're trying to use this
calculated column in an invalid manner.
 
You will have to do this calc in a query, rather than the table.

I tried it but I still getting this message:

InvalidSQL syntax-cannot use multiple columns in a column-
level CHECK constraint
-----Original Message-----
Try this....

=(now()-[birthdate])/365.25.

Using 365.25 accounts for leap years.
Then format the result however you wish.

Kind regards
Deb Roberts

Joe Clarke said:
The formula should work as long as the [Birthdate] field is stored as a
date. Put a right bracket after the "[Birthdate".

It doesn't matter how the date is displayed as long as it's stored as a
date.


I would like to find a code for a table to automatically
calculate the age. I would like to do it in the table. I
found code for doing it in the form but from the short
date format. I cannot and do not want to change the date
format.

the code for the form is as follows: =DateDiff("yyyy",
[Birthdate,Now())+Int(Format(now(),"mmdd")<Format
([Birthdate],"mmdd"))

I tryed playing with it around but I cannot get it to work.

Thank you.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top