help needed with some code

[Spier2vb]

help needed with some code

Please help me with this code I have one table [Employee] include
Emp-ID
Emp-Name
HireDate
ExperienceDate

and this code calculated the total experience of each employee dependence of
current date and this code working properly but I have one problem this code
doesn’t update the experience date.

Private Sub ExperienceDate_AfterUpdate()
Dim intHold As Integer
Dim dayhold As Integer

If Day(Me.CurrentDate) < Day(Me.HireDate) Then dayhold = DateDiff("d",
Me.HireDate, DateSerial(Year(Me.HireDate), Month(Me.HireDate) + 1, 0)) +
Day(Me.CurrentDate)
Else
dayhold = Day(Me.CurrentDate) - Day(Me.HireDate)
End If
Me.dteExp = LTrim(Str(intHold \ 12)) & " : " & LTrim(Str(intHold Mod 12))
& " : " & LTrim(Str(dayhold) & " ")
End Sub

Example:
CurrentDate = 19/02/2009
HireDate = 07/12/1981
Result is:
year months days
ExperienceDate = 27 : 02 : 12


And the next day must the ExperienceDate to be 27 : 02 : 13 this my problem
I need code update the data, what can I do and how can I refresh the data

And how can I make the code check for current date every day

Spider

 

[Tom van Stiphout]

On Thu, 19 Feb 2009 02:51:01 -0800, Spier2vb

This is a perfect example of how NOT to do it. ExperienceDate is a
value that changes every day. It is a calculated value based on Today
and the HireDate. It should be calculated in a query whenever you need
it, rather than saved to a table. That query can use an expression or
call a function to make the calculation. Personally I would probably
do the ExperienceDate as a fractional value:
DateDiff("yyyy", HireDate, Date())
For your example this may return 27.14 which is close enough to
understand this person is very experienced.

-Tom.
Microsoft Access MVP