Automatically calculate field

[Del_Piero_3]

Hi guys,

I have a mainform "frm_CaseReference" and a subform "subfrm_CasesControls".

In the main form I have "DOB" field and in the subform I have "DateSlideTaken" and "AgeAtSmear" fields.

I want to automatically calculate age in the "AgeAtSmear" from the "DOB" and "DateSlideTaken" but having problems.

I have tried the following code but it doesnt work:

=DateDiff("yyyy",Forms!frm_CaseReference!DOB-[DateSlideTaken],Now()

I have read it is not good idea to store age but my work place want this so i have to include it. Can someone please help....

 

[raskew]

It may seem that the DateDiff() function should return Age based on Date of Birth (DOB) and a user-specified date. However, there's a problem with that solution since the DateDiff("yyyy"....) merely subtracts one year from another.

To accurately return age in years,we must know whether the month/day portion of the target date is >= than that of the DOB. We can add a boolean statement that examines month/days of the two dates and returns -1 (false) or 0 (true) to indicate if the month/day of the target date meets or exceeds that of the DOB. Here's the expression:

Age = DateDiff("yyyy", DOB, Target) + (Target < DateSerial(Year(Target), Month(DOB), Day(DOB)))

To use, replace DOB and Target with your field names.

HTH - Bob

 

[Del_Piero_3]

Age = DateDiff("yyyy", DOB, Target) + (Target < DateSerial(Year(Target), Month(DOB), Day(DOB)))

To use, replace DOB and Target with your field names.

HTH - Bob

Raskew thank you very much for the help. It worked like a charm....Anyway below is my code, it may help someone else in future....

=DateDiff("yyyy",[Forms]![frm_CaseReference]![DOB],[DateSlideTaken])+([DateSlideTaken]<DateSerial(Year([DateSlideTaken]),Month([Forms]![frm_CaseReference]![DOB]),Day([Forms]![frm_CaseReference]![DOB])))