Age based on previous years.

[n2nuk]

Hi there,

Using Access 2003

I need is to produce a listing of clients that were aged 13 to 19 between 01/04/2004 and 31/03/2005.

I would also need to display their age at that time.

I have used a where clause that reads 'where dob is greater than 31/03/1984 and dob is less than 01/04/1991'

Although this method seems to work, it requires a fair bit of time to work out the dob date range, also this does not provide the clients age on thier birthdate in that date range.

Any help or assistance greatly appreciated.

Regards

Naz

 

[dhookom]

There is a function for returning age at

http://www.mvps.org/access/datetime/date0001.htm.

The arguments are two dates such as DOB and the reference date.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

I would also need to display their age at that time.
At which time ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[n2nuk]

I had tried to describe this requirement on my original post but on re-reading it did not make sense!

I'll go the for clients age on the 1st of September for the year that is being reported on on.

So for the 2004/2005 report I need to return the clients age on the 1st of September 2004. (I need to run similar reports for 2005/6 and 2006/7 periods)

Not sure if that makes it easier...

I have also copied the code from dhookom, but need some advise on where I would insert the code. A brief rundown on how and where to add the code to my query would be most helpful.

Thanks

Naz

 

[dhookom]

You can use the Age() function almost anywhere you would use any other function. For instance in a query: create a new column
AgeAsOf: Age([DOBField],#09/01/2004#)
This would create a column in the query filled with the age as of Sept 1, 2004.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]