Calculating Age on continuous Form

[spartansFC]

Hi

I'm trying to calculate a persons age based on their Date of birth and the first date of each month,
but when i try i get some odd outcomes. I can calculate a persons age on a single form but on
a continuous form i can't, anyone know why. Here's the details:

i have a table called tblInvoiceChildrenSubAmount where all the months and amounts are stored, it's based off a
query which pulls in DOB info etc. On tblInvoiceChildrenSubAmount i have lngInvoicesMonth
a combo box (linked to tblInvoiceMonths includes StrInvoiceMonth and dteInvoiceMonth) so the
data on tblInvoiceMonths looks like:

strInvoiceMonths: April 2011, May 2011....
dteInvoiceMonth: 01/04/2011, 01/05/2011....

the query is then used on frmInvoicesChildrenSubPayments whereby i have a combo box (lngInvoicesMonth), the user
selects a month and on each continuous line the correct DOB and dteInvoiceMonth are pulled through but they are hidden
from the user (they're just there to do the calculation.

I've also created 4 unbound text boxes

today: =date()
Months_txtbox: =DateDiff("m",[DOB],[dteInvoceMonthActual])+Int(Format([dteInvoceMonthActual],"dd")<Format([DOB],"dd"))-([Year_txtbox]*12)
Year_txtbox: =DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0)

all the above is then pulled into another unbound text box which displays the age:

ChildAge_txtbox: =[Year_txtbox] & "." & [Months_txtbox]

but it doesn't seem to work in all cases, i've included 2 examples below of what can happen:

DOB dteInvoiceMonth Database Value What it Should be
10/05/2008 01/05/2011 3.-1 2.11
10/05/2008 01/06/2011 3.0 3.0
10/05/2008 01/07/2011 3.1 3.1
10/05/2008 01/08/2011 3.2 3.2
10/05/2008 01/09/2011 3.3 3.3
10/05/2008 01/10/2011 3.4 3.4
10/05/2008 01/11/2011 3.5 3.5
10/05/2008 01/12/2011 3.6 3.6
10/05/2008 01/01/2012 3.7 3.7
10/05/2008 01/02/2012 3.8 3.8
10/05/2008 01/03/2012 3.9 3.9

so the above data, the first line displays 3.-1 amount for the age?

07/01/2007 01/05/2011 4.3 4.3
07/01/2007 01/06/2011 4.4 4.4
07/01/2007 01/07/2011 4.5 4.5
07/01/2007 01/08/2011 4.6 4.6
07/01/2007 01/09/2011 4.7 4.7
07/01/2007 01/10/2011 4.8 4.8
07/01/2007 01/11/2011 4.9 4.9
07/01/2007 01/12/2011 4.10 4.10
07/01/2007 01/01/2012 4.11 4.11
07/01/2007 01/02/2012 4.12 5.0
07/01/2007 01/03/2012 4.13 5.1

the above data, if the months get to 12, it's supposed to increase the year by one, then the months go back to zero but it just carries on, in other data i've seen the age go upto 4.19?

So can anyone tell me what i'm doing wrong, sorry for the long description, just wanted to explain everything.

Thanks

Mikie

 

[ZmrAbdulla]

For a continues form you will have to use a query as the recordsource.This query will have additional columns that calculate whatever calculations you need.

Zameer Abdulla

 

[dhookom]

Mikie,
That's a fairly weird age format. I would use Doug Steele's A More Complete DateDiff Function in your record source query or as the control source of your text box.

You can modify the code to format the returned value as you like.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

thanks for the responses ZmrAbdulla and Duane, ii'll try these out

Mikie