Date field subtract and insert it into a number field

[akalehzan]

Hi All,

In MS ACCESS I have a table with three fields:
ID_DATE_START
ID_DATE_END
ID_AGE

Both ID_Date_Start and ID_DATE_END property are Date format, and ID_AGE property is Number.
I like to subtract ID_DATE_END from ID_DATE_START
And insert the result in ID_AGE field.
Let’s say ID_Date_Start is: 12/01/2000 and ID_DATE_END is: 12/01/2007
Then the result should be:
ID_AGE= 7.0

Thanks For any help.

Abrahim

 

[pbaldy]

Generally, since the age can be calculated from the other 2 values, it should not be saved. Simply calculate it when you need it. You can use the DateDiff function to do that, more info in Help.

 

[RuralGuy]

You should really *not* store this type of calculated value for a number of reasons; just calculate the value whenever you need it. To that end, here's a function that you can use to achieve that.

Public Function Age1(dteDOB As Date, Optional SpecDate As Variant) As Integer
   Dim dteBase As Date, cust_age As Date, cust_birthday As Integer
   If IsMissing(SpecDate) Then
      dteBase = Date
   Else
      dteBase = SpecDate
   End If
   cust_birthday = DateDiff("yyyy", dteDOB, dteBase)
   cust_age = DateSerial(Year(dteBase), Month(dteDOB), Day(dteDOB))
   Age1 = cust_birthday + (dteBase < cust_age)
End Function

...and here's a link that describes the DateTime field for you.

 

[akalehzan]

Thanks for the help!

Abrahim

 

[RuralGuy]

Glad we could help.