Difference between two dates in MS ACCESS2007

[mohamed aqeel]

Hi,

In my database I need to find the Experience of an employee based on his DOJ. So I have used a formula

Round((((Year(Date) - Year([DOJ])) * 365 + (Month(Date) - Month([DOJ]) * 30.4 + Day(Date) - Day([DOJ])) / 365),1)

in the control source property of my field Exp.and it worked well, i got the experience with the fraction part.But I was unable to store that value in Tables.So I modified the code little and inserted in the After Update event of the DOJ field.

the code is,

Private Sub DOJ_AfterUpdate()
Me.EXP = Round((((Year(Date) - Year(Me.DOJ)) * 365 + (Month(Date) - Month(Me.DOJ)) * 30.4 + Day(Date) - Day(Me.DOJ)) / 365), 1)
End Sub

but now i am not getting the fractional part.I am getting the experience value as a whole number only.

Eg:
DOJ = 11-May-88
EXP = 24

Please help me to solve this issue.

THANKS IN ADVANCE!!!

 

[Galaxiom]

Try this instead:

=Round((DateDiff("d", Me.DOJ, Date())/365.25),1)

 

[mohamed aqeel]

Hi Galoxiom,

I tried that one.. Same output:
eg:
DOJ = 10-Jun-09
EXP = 3.0
I want the output as 2.7.
Is there any other way to achieve that??

 

[Galaxiom]

It works fine for me in the Immediate window.

If you had the field in the table set as an Integer and the Format property of the textbox as 0.0 you would get that result.

However it seems you are storing a calculated value. This would be a breach of normalization.

 

[mohamed aqeel]

Thank u got the result!!