Want age in years with decimal I.E. 3.5 years

[Bmelville1]

Hello,
I have searched all over and cannot figure out how to do this, I think it should be simple. I have a table that lists computers I want to know the age of them for replacement purposes. I am using this function to populate a textbox on a form that updates my table. the results I am getting is a negative whole number I would like to get a positive number with one decimal place. so if computer was shipped September 24, 2010 my textbox would say 3.5 not -3

here is my function

Private Sub Form_Load()
Dim theDate As Date
Dim age As Integer
theDate = Nz(Me.compDate.value, 0)
If theDate > 0 Then
              age = DateDiff("yyyy", Now(), theDate)
              Me.compAge = age 
End If
End Sub

 

[plog]

I am using this function to populate a textbox on a form that updates my table.

First, that's not how databases should work. You shouldn't store calculated values, instead you should calculate them when you need them. So, don't store age in a table.

As for your calculation, you are asking DateDiff (http://www.techonthenet.com/access/functions/date/datediff.php) to return the difference in whole years, so that's why you aren't getting fractional amounts. You will need to change the first argument of your DateDiff function to more grainular unit (e.g. months or days) and then do some math to convert that unit into fractional years.

Also, to make the values positive you should swap your two date arguments (the 2nd and third) so that the comparison is done properly. Your DateDiff function should be replaced with this line of code:

age = Round(DateDiff("d", theDate, Now())/365,1)

 

[Bmelville1]

Thanks that helped with the negative number, but I still have problems with the numbers being whole not decimals. The only reason I was saving the age to the table was to run a report based on old computers. I agree age shouldn't be a stored value. Was just trying to get it to work before writing the code for generating my report/order for new computers.

 

[plog]

That code I gave you should calculate it with 1 decimal spot. Put this code right below what I just gave you to see what age actually is:

MsgBox(age)

It might be something with the form element it is going into.

 

[Bmelville1]

Plog,
fixed it it was my error I had

Dim age As Integer

I changed it to double and it worked thanks for your help.