Simple or NOT Age Calculation

[vadharah]

Auto Age Calculation

when you think you getting the hag of access it goes wrong.

I have a table with a field called DOB (Date of Birth) i have now added a new column called Age.. What i want to do is automatically calculate the Age (based on DOB) and keep it in this column..Is this possible?? if so where do i put the function.

I have tried placing the function in the design view under default value. What i put was

=DateDiff("yyyy","DOB", Date())

Im getting an error saying "Database engine doesnt recognize DOB"

any help appreciated

Thank you in advance

 

[MSAccessRookie]

when you think you getting the hag of access it goes wrong.

I have a table with a field called DOB (Date of Birth) i have now added a new column called Age.. What i want to do is automatically calculate the Age (based on DOB) and keep it in this column..Is this possible?? if so where do i put the function.

I have tried placing the function in the design view under default value. What i put was

=DateDiff("yyyy","DOB", Date())

Im getting an error saying "Database engine doesnt recognize DOB"

any help appreciated

Thank you in advance

Is there a reason to keep the value? Each time you access the DOB, the Age is subject to change. The function(s) calculating the age do not need to store data which will be obsolete and require changing almost as soon as it is created, when it can be recalculated on the fly for whenever it is needed.

If you really need to, try the full name fo the field (YourTableName.DOB) instead of the column name alone.

 

[raskew]

The following will compute age in years as of the specified dteEnd. If dteEnd is not specified, it computes as of the current date.

Age should not be stored, but calculated as needed.

Function fAge2(DOB As Date, Optional dteEnd As Variant) As Integer
're: http://www.access-programmers.co.uk/forums/showthread.php?t=116432
'coded by: raskew
'Inputs:  1) ? fAge2(#4/13/53#, #10/23/06#)
'         2) ? fAge2(#11/1/53#, #10/23/06#)
'         3) ? fage2(#4/13/53#)
'Outputs: 1) 53
'         2) 52
'         3) 55

   dteEnd = IIf(IsMissing(dteEnd), Date, dteEnd)
   fAge2 = DateDiff("yyyy", DOB, dteEnd) + (DateSerial(year(dteEnd), month(DOB), day(DOB)) > dteEnd)

End Function

Note: + (DateSerial(year(dteEnd), month(DOB), day(DOB)) > dteEnd) is a boolean statement that equates to -1 if True, 0 if False.

HTH - Bob

 

[vadharah]

Thank you for the reply and advice guys! much appreciated