Wrong number returned to table from calculation query

Question

Hi

I have a table: tblChild

One of the fields in this table is ChildAge; data type = Number; Field size = Integer; Decimal Places = 2

I have an Update Query: qryChildAge

Field = ChildAge

Table = tblChild

Update To = Round(DateDiff("d",[tblChild].[ChildDoB],Now())/365.202,2)

For the most part the query works except it doesn't display the result with an age in years with a decimal for the current year. For instance a child is 9.6 years old but either the query returns the calculation wrong showing 10 (a whole number), or my ChildAge has a wrong data type, or my query is inadequate (which I am happy to admit too as I'm newish at this.

Any help would be greatly appreciated.

Thank you in advance

Gene Lawrence

Budding App Builder in Old Age

Answer 1

Because it's a core design rule for a database - indeed when a simple select query (as shown above) will provide the data at all times without having to update anything.

Answer 2

Hi Scott

To help my learning would you please explain why it is best not to store a calculated field in a table? I dont understand why, and the age of the child is needed to he shown.

Thank you

Gene

Answer 3

As soon as I saw datatype of integer, In knew the answer to why it is rounding. Gustav explained that.

But I want to reiterate that you should not even have this field in your table. As a generally rule calculated values are not stored. Instead calculations should be done in queries on the fly.

Answer 4

Change the field type to Double as Integer cannot hold decimals.

However, you should not update a table as the age will change daily.

Use a simple select query, and no update is needed:

Select *, TotalYears([ChildDoB], Date()) As Age

From tblChild

Here, a function is used:

' Returns the decimal count of years between Date1 and Date2.

'

' Rounds by default to three decimals, as more decimals has no meaning

' because of the leap years.

' Optionally, don't round, by setting Round3 to False.

'

' 2017-01-24. Gustav Brock, Cactus Data ApS, CPH.

'

Public Function TotalYears( _

    ByVal Date1 As Date, _

    ByVal Date2 As Date, _

    Optional Round3 As Boolean = True) _

    As Double

    Dim Years       As Double

    Dim Part1       As Double

    Dim Part2       As Double

    Dim Fraction    As Double

    Dim Result      As Double

    Years = DateDiff("yyyy", Date1, Date2)

    Part1 = (DatePart("y", Date1) - 1) / DaysInYear(Date1)

    Part2 = (DatePart("y", Date2) - 1) / DaysInYear(Date2)

    If Round3 = True Then

        ' Round to three decimals.

        Fraction = (-Part1 + Part2) * 1000

        Result = Years + Int(Fraction + 0.5) / 1000

    Else

        Result = Years - Part1 + Part2

    End If

    TotalYears = Result

End Function

' Returns the count of days of the year of Date1.

'

' 2016-02-14. Gustav Brock, Cactus Data ApS, CPH.

'

Public Function DaysInYear( _

    ByVal Date1 As Date) _

    As Integer

    Dim Days    As Integer

    Days = DatePart("y", DateSerial(Year(Date1), 12, 31))

    DaysInYear = Days

End Function

Answer 5

ciao Gene,

I would be inclined to apply a UDF function to achieve your taskl like :

Public Function age(ByVal dtDB As Date, Optional ByVal dtDate As Date = 0) As Integer

If dtDate = 0 Then dtDate = Date

age = DateDiff("yyyy", dtDB, dtDate) + (dtDate < DateSerial(Year(dtDate), Month(dtDB), Day(dtDB)))

End Function

and invoking it into a query instead than a calculated field on a table :

SELECT Impiegati.IDImpiegato,

             Impiegati.Cognome,

             Impiegati.Nome,

             Impiegati.DataNascita,

             age([dataNAscita]) AS eta

FROM Impiegati;

ciao, Sandro.