I have the following sample field and record

Age   ApprovedDate
40     15-05-2000

Age-number field that shows the current age in 2010
Approveddate - date field when the application was approved.

How to calculate the age of the applicants at the point of approval. In the 
above  example the age would be 30...

Thanks

On Thu, 13 May 2010 17:41:01 -0700, zyus <zyus@discussions.microsoft.com>
wrote:

>I have the following sample field and record
>
>Age   ApprovedDate
>40     15-05-2000
>
>Age-number field that shows the current age in 2010
>Approveddate - date field when the application was approved.
>
>How to calculate the age of the applicants at the point of approval. In the 
>above  example the age would be 30...
>
>Thanks
>

The Age field should SIMPLY NOT EXIST in your table. If it's right today, it
will be wrong for every person in your database in 12 months. I am 63 years
old in 2010; I'll also be 64 years old in 2010 (in another week in fact).

How was this field derived? What does it MEAN - was the person 40 on January
1, or on December 31, or what? If the person's birthday was 14-05-1970 you're
"age at point of approval" would be 30, if their birthday were the 16th it
would be 29; which answer do you want?

-- 

             John W. Vinson [MVP]

Thanks for your reply,

The data is imported from text files that extracted from other system and 
calculated on Jan 1 basis.

The data is for analysis purpose and will be updated/replaced every month.

 

"John W. Vinson" wrote:

> On Thu, 13 May 2010 17:41:01 -0700, zyus <zyus@discussions.microsoft.com>
> wrote:
> 
> >I have the following sample field and record
> >
> >Age   ApprovedDate
> >40     15-05-2000
> >
> >Age-number field that shows the current age in 2010
> >Approveddate - date field when the application was approved.
> >
> >How to calculate the age of the applicants at the point of approval. In the 
> >above  example the age would be 30...
> >
> >Thanks
> >
> 
> The Age field should SIMPLY NOT EXIST in your table. If it's right today, it
> will be wrong for every person in your database in 12 months. I am 63 years
> old in 2010; I'll also be 64 years old in 2010 (in another week in fact).
> 
> How was this field derived? What does it MEAN - was the person 40 on January
> 1, or on December 31, or what? If the person's birthday was 14-05-1970 you're
> "age at point of approval" would be 30, if their birthday were the 16th it
> would be 29; which answer do you want?
> 
> -- 
> 
>              John W. Vinson [MVP]
> .
> 

On Thu, 13 May 2010 21:11:01 -0700, zyus <zyus@discussions.microsoft.com>
wrote:

>Thanks for your reply,
>
>The data is imported from text files that extracted from other system and 
>calculated on Jan 1 basis.
>
>The data is for analysis purpose and will be updated/replaced every month.
>
> 

Please answer my question:

>What does it MEAN - was the person 40 on January
> 1, or on December 31, or what? If the person's birthday was 14-05-1970 you're
> "age at point of approval" would be 30, if their birthday were the 16th it
> would be 29; which answer do you want?
> 

Having an AGE field in your table is HOPELESSLY AMIGUOUS.

It makes it impossible, even in principle, to get an exact age as of the point
of approval. The information to provide that answer simply does not exist. So
you'll have to settle for an approximation; I'm asking you - which
approximation do you want?

Try

Year(Date()) - Year([DateOfApproval]) + [Age]

to get an approximation. It'll be wrong half the time by one year, but you
can't do better than that with the information you've posted.
-- 

             John W. Vinson [MVP]

On Thu, 13 May 2010 21:11:01 -0700, zyus <zyus@discussions.microsoft.com>
wrote:

>The data is imported from text files that extracted from other system and 
>calculated on Jan 1 basis.
>

I was 63 on January 1.

How old am I today? Am I 63 or 64? Might be either, depending on my birthday.

Do you get my point? Your question, as posted and with the data that you have
available, *cannot be answered*.
-- 

             John W. Vinson [MVP]

Thanks John,

I missed one points during my earlier explaination. The data that i imported 
every month has a month end position which is month & year (dated 31st).

Can the month end date be used to calculate the date.

"John W. Vinson" wrote:

> On Thu, 13 May 2010 21:11:01 -0700, zyus <zyus@discussions.microsoft.com>
> wrote:
> 
> >The data is imported from text files that extracted from other system and 
> >calculated on Jan 1 basis.
> >
> 
> I was 63 on January 1.
> 
> How old am I today? Am I 63 or 64? Might be either, depending on my birthday.
> 
> Do you get my point? Your question, as posted and with the data that you have
> available, *cannot be answered*.
> -- 
> 
>              John W. Vinson [MVP]
> .
> 

On Sun, 16 May 2010 17:35:01 -0700, zyus <zyus@discussions.microsoft.com>
wrote:

>Thanks John,
>
>I missed one points during my earlier explaination. The data that i imported 
>every month has a month end position which is month & year (dated 31st).
>
>Can the month end date be used to calculate the date.

To within a month, yes.

To within a day, no.

This is not an Access issue, it's an issue of logic.

If all you know is that my birthday occurs during the month of May, on an
unspecified date, it is logically IMPOSSIBLE to determine my age today. You
can determine my age on the 1st (the younger age of the two possibilities);
you can determine my age on the 31st (the older). For any other date you
simply don't have enough information to do so.


-- 

             John W. Vinson [MVP]

Hi John,

Sorry, i've missed out one important info, the data that i imported every 
month has a month end date position, say April data the monthenddt is 
30-04-2010. Is there any possibility to calculate age at approval

"John W. Vinson" wrote:

> On Thu, 13 May 2010 21:11:01 -0700, zyus <zyus@discussions.microsoft.com>
> wrote:
> 
> >The data is imported from text files that extracted from other system and 
> >calculated on Jan 1 basis.
> >
> 
> I was 63 on January 1.
> 
> How old am I today? Am I 63 or 64? Might be either, depending on my birthday.
> 
> Do you get my point? Your question, as posted and with the data that you have
> available, *cannot be answered*.
> -- 
> 
>              John W. Vinson [MVP]
> .
> 

On Mon, 17 May 2010 17:43:01 -0700, zyus <zyus@discussions.microsoft.com>
wrote:

>Hi John,
>
>Sorry, i've missed out one important info, the data that i imported every 
>month has a month end date position, say April data the monthenddt is 
>30-04-2010. Is there any possibility to calculate age at approval

I don't know how I can say it any clearer, Zyus.

NO, THERE IS NO POSSIBILITY TO CALCULATE THE AGE AT APPROVAL.

Sorry for shouting, but it's just not getting through.

Again, just in case I'm misunderstanding:

Suppose my age as of April 1 were 63, and my age as of May 1 were 64.

The data of approval is April 15.


You *cannot determine my age on that date*.  It might be 63 (if my birthday
comes on or after the 15th), or it might be 64 (if my birthday comes earlier).

The best you can do is to plus or minus one year. If that's good enough, then
sure. If it's not, then you simply do not have enough information.
-- 

             John W. Vinson [MVP]