I am trying to calculate a persons age from DOB to a specific year afer "Enter date"

[don1.delinquent]

I have created the extra field in my query for "Age" but I can't seem to be able to calculate it to show me in years. I am very new at this, I have tried Date(Now()-[DOB]) and other examples from different sites and have no luck

can someone please assist me with this



Regards
Don

[amrut]

Try with DateDiff function
http://www.techonthenet.com/access/f...e/datediff.php

[don1.delinquent]

Thank you 'amrut' that was very helpful I'm now on the right track. I tried DateDiff("yyyy",[Dob],Date()) and it gave me the Age as of this year but if I type in another year it still comes up with this years age. Any suggestions to fix this

regards
Don

[amrut]

but if I type in another year it still comes up with this years age.

How do you type another year ? Further, remember that access calculates all dates based on US date format of mm/dd/yyyy irrespective of your PC's date format.

[bayoeth]

Thank you 'amrut' that was very helpful I'm now on the right track. I tried DateDiff("yyyy",[Dob],Date()) and it gave me the Age as of this year but if I type in another year it still comes up with this years age. Any suggestions to fix this

regards
Don

Hi,here's what i found in the internet for computing the Age.

In your table, create an Age Field. Go to design view and make it a Calculated Field. On the Expression, type ([Your Desired Date]-[Dob])*4/1461

After that, create another Field to Round Down the Age. On the Expression type Left([Age],2)

That formula somehow worked for me, the only problem would be it's rounded to nearest Year, and you'll get Zeros for those who are less than 1 year old.
Another Problem I found is that there are instances when it calculates 1 year less than the Age. For example DoB is 4/18/1952 and Future Date is 4/18/1957, it returns only 4 (or 4.99 if you did not use 0 for the decimal format in your report). i don't know why that happens

[ssanfu]

I use a function to calculate ages.

Function GetAge(varDOB As Variant, Optional varAsOf As Variant) As Variant
   'Purpose:   Return the Age in years.
   'Arguments: varDOB = Date Of Birth
   '           varAsOf = the date to calculate the age at, or today if missing.
   'Return:    Whole number of years.
   Dim dtDOB As Date
   Dim dtAsOf As Date
   Dim dtBDay As Date  'Birthday in the year of calculation.

   GetAge = Null          'Initialize to Null

   'Validate parameters
   If IsDate(varDOB) Then
      dtDOB = varDOB

      If Not IsDate(varAsOf) Then  'Date to calculate age from.
         dtAsOf = Date
      Else
         dtAsOf = varAsOf
      End If

      If dtAsOf >= dtDOB Then      'Calculate only if it's after person was born.
         dtBDay = DateSerial(Year(dtAsOf), Month(dtDOB), Day(dtDOB))
         GetAge = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
      End If
   End If
End Function

Usage can be on a form. In a text box, I have

=Getage([Forms]![employee details]![end_dob],[Forms]![startup]![viewdate])

or you can use the function in a query......