how to update age of employees on button click

[rahul2310]

i have table in which there are two thousand employees.
In form i select date of birth based on which age gets calculated.
after one year i again have to open form and select date of birth to again update age,which is time consuming as their are so many employees.
Is it possible to update age for all employees just by button click.
what will be code for that.
thank you

[rajendran r]

Hello Rahul2310,
I think if you put the following update sql code in command_clik(), it will work
---------------------------

Private Sub YOUR_COMMAND_Click()
Dim strSql As String

strSql = "UPDATE [YOUR_TABLE]" & _
    "SET [YOUR_TABLE].AGE = DateDiff(""YYYY"",[DOB],Now());"

DoCmd.RunSQL strSql

End Sub

--------------------------

[ADezii]

Age should not exist as a Field in your Table. Instead, set the Data Source of your Form to a Query that automatically calculates the Age based on an Employee's DOB.

[NeoPa]

Apart from the fact that ADezii has already explained, that you should never store ages, the SQL code suggested would be unreliable because :

• The SQL is not correctly formatted (It misses a space after [YOUR_TABLE]). You may get away with it as SQL interpreters are clever, but it's not good.
• Using the 'yyyy' parameter (as it's correctly specified) will provide a value which is not consistent with how ages work. It will round to the nearest rather than counting whole years only.

We applaud the intention of providing help, but please be careful not to provide information that is misleading and will lead people to do things the wrong way.

[rajendran r]

thanks for the information

[zmbd]

And just because we should have a reference to a method that calculates the age correctly:

Provided by Allen Browne: Age() Function

Option Compare Database
Option Explicit

Function Age(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.

    Age = 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))
            Age = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
        End If
    End If
End Function

Insert this into a standard module and then it can be used in controls and query fields.