Calculate "Age" from a Date of Birth and add to ta

[chubbychap]

Hi

I have a table for storing customer basic details and have a form based on the table, to enable the data to be input.

I have a "Date of Birth" field in my table and need to show the current Age in the "Age" field as well. I have figured out how this can be done when in a form using the following expression in the Control Source of the"Age" field properties:

=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))

However, although this will show the calculated Age in the Form view, it doesn't seem to transfer it into the "Age" field of the Table.

Is there any way to get the Age to show in the table? Please note that I am not VB proficient.

Many thanks for your help.

Chubbychap

 

[vanilladan]

Re: Calculate "Age" from a Date of Birth and add t

Hi ChubbyChap,

The value in the form.... only exists within the form (as you've realised).

The way to transfer this data into your Table is using an update query.
You would need to do the following.

Create an AGE field in your Customer Details table, if you don't already have one.

Set up an UPDATE Query based on your Customer Details table.
Select the AGE field and enter an expression in the UPDATE field - using exactly the same expression as you did in your form.

When you run this query, the AGE field in your Customer Details table will be updated.

NB - because AGE is a type of data that will change every day, you will need to run this UPDATE query regularly to keep your data up to date.

Hope this works out for you - any probs, give me a shout.

Vanilladan

 

[chubbychap]

Re: Calculate "Age" from a Date of Birth and add t

Vanilladan

You are a star. It works. Many thanks for your help

Cheers

Chubbychap

 

[Target]

Re: Calculate "Age" from a Date of Birth and add t

Hello
I have same requirement except I have years of services employee worked with us. I use the age code to calculate number of years,

Joining Date = 20/Spt/1997

=DATEDIF(G112;NOW();"y") & " years, " & DATEDIF(G112;NOW();"ym") & " months, " & DATEDIF(G112;NOW();"md") & " days"

Result =5 years, 8 months, 19 days


I would like to calculate number of months from joining date until 31/March/2003

I'll be highly appreciated for any help :wink: