Truncating numbers from a calculted field

[thebowler]

When calculating the age from the birthdate field and the Date()function, the age rounds up or down, as appropriate. (When the decimal place is set to "0&quot How can I make the age field truncate every number to the right of the decimal instead of rounding up or down, so it shows the age in whole numbers.

 

[BoxHead]

The following function comes from the Microsoft Knowledge Base, Topic # Q100136. It is for Access '97, but they also have articles for 2000 & 2002.

If you paste the blue text into a new Module of its own you can call it from any query, form or report in the database.

EG: Your table's Date of Birth field is named "DateOfBirth". In a query that is based on your table, type the following into the 'Field' line of the Query Grid:

    Current Age: Age([DateOfBirth])

When you run the query, there will be a column in your result set called "Current Age" containing the correct age for each record that includes a DateOfBirth value.

In an unbound textbox in the design view of a form or report, type:

 = Age([DateOfBirth])

This will calculate the correct age for each record.


Function Age (varBirthDate As Variant) As Integer
Dim varAge As Variant

If IsNull(varBirthdate) then Age = 0: Exit Function

varAge = DateDiff("yyyy", varBirthDate, Now)
If Date < DateSerial(Year(Now), Month(varBirthDate), _
Day(varBirthDate)) Then
varAge = varAge - 1
End If
Age = CInt(varAge)
End Function

A function like this usually works best because you never have to save a calculated value that could change tomorrow.

HTH
John

Use what you have,
Learn what you can,
Create what you need.

 

[pdldavis]

Hi, if it's calculating correctly for you and you simply want to show the age in numbers sans decimals - since most ages are under 100,

=left([age],2) might work for you. Displays the two leftmost characters of the string.

 

[lameid]

Int()

That's the floor function in Basic folks. It returns the integer portion of a number.