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

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

 

[Joe Clarke]

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.

 

[Allen Browne]

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

 

[Deb Roberts]

Try this....

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

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

Kind regards
Deb Roberts

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.

 

[Guest]

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

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.

.

 

[Guest]

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.

.

 

[John Vinson]

I tried it but I still getting this message:

InvalidSQL syntax-cannot use multiple columns in a column-
level CHECK constraint

Please post your SQL. It appears that you're trying to use this
calculated column in an invalid manner.

 

[Deb Roberts]

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

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.

.