Handling of null return in number field on form?

[Broons Bane]

Hi all

I have a calculated field in a form which calls a function to
calculate somebody's age, based on a date of birth field in the same
form. the problem is that it is valid for date of birth to be not
entered. thus, my function returns a null to the age field which is
number and i get an error. i could return 0, but don't want age of
zero years to display, not to mention that some babies can have an age
of 0 years! Any thoughts? Thanks

 

[John Spencer]

Why do you get an error? Is the function causing the error?

Can the function return null? If the function is defined to return a
number change it to allow it to return null

Public Function fGetAge(DOB, TargetDate) as Long

would need to be changed to

Public Function fGetAge(DOB, TargetDate) as Variant

so it can return null OR a number

Also, the code in the function must be able to handle null.

If IsDate(DOB) = False
fGetAge = Null
ELSE
'do the calculation
fGetAge = ....
End if

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Tom van Stiphout]

On Sun, 17 May 2009 06:34:39 -0700 (PDT), Broons Bane

You need to make that age field nullable in the table design, so it
can accept nulls.
Btw, you realize that after you enter age, the next day on average one
in 365 records will have an incorrect age, and in two days 2/365, etc.
It's typically MUCH better to store a birthdate, and calculate age
on-the-fly in a query:
select DateDiff("yyyy", Date, myBirthDate)
from myTable

-Tom.
Microsoft Access MVP

 

[Broons Bane]

Whether or not you should store the age as a value in a column in the table
is a question of functional dependence.

1.  If the table is modelling people then the age is not functionally
dependent solely on the key of the table, but also on the date of birth, so
is redundant as (a) it can be computed at any time from the date of birthand
(b) it will contain inconsistent data if not updated as time passes.  The
table is consequently not normalized to Third Normal Form.

2.  If on the other hand the table is modelling something such as an event in
a person's life then it is functionally dependent solely on the key of the
(events) table, so is not redundant.  However, a more usual approach insuch
a case would be to store the date of the event, in which case the age canbe
computed from the event date and the person's date of birth, so to store the
age as well as the event date would again be redundant, and once again the
table would consequently not normalized to Third Normal Form as in this case
the age is functionally dependent on the combination of the key and a non-key
column (the event date).

If scenario 2 above is what you are handling here, and you opt to store the
age at the time of the event rather than the date of event the simply allow
the age column to contain Nulls.

If you wished to adhere fundamentally to the theoretical principles of the
relational model, and prohibit Nulls altogether, then the solution is to
store the date of birth in a separate related table, in which case rather
than having a Null date of birth there would be no row in the related table.
The same goes for age in scenario 2.  This would in my view be taking a
ridiculously purist approach, however.

Ken Sheridan
Stafford, England

Hi guys

Thanks for your replies so far

For the form purposes, I calculate age 'now' and age as at 1/1/this
year, neither of them are database fileds as such, just useful info
for the person using the form, *if* the optional date of birth happens
to be entered on the table for that person.

the function is as follows (which I probably copied from the web)

Function Age(varDOB As Variant, Optional varAsOf As Variant) As
Variant
'Purpose: Return the Age in years.
'Arguments: varDOB = Date Of Birth
' varAsOf = the date to calculate the age at, or today
if missing.
'Return: Whole number of years.
Dim dtDOB As Date
Dim dtAsOf As Date
Dim dtBDay As Date 'Birthday in the year of calculation.
Age = Null 'Initialize to Null

'Validate parameters
If IsDate(varDOB) Then
dtDOB = varDOB

If Not IsDate(varAsOf) Then 'Date to calculate age from.
dtAsOf = Date
Else
dtAsOf = varAsOf
End If

If dtAsOf >= dtDOB Then 'Calculate only if it's after
person was born.
dtBDay = DateSerial(Year(dtAsOf), Month(dtDOB), Day
(dtDOB))
Age = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
End If
End If
End Function

Hope the extra info helps...

 

[Broons Bane]

I don't see the problem.  If the DoB is Null then the computed control
showing the return value of the function will be Null.  Where does the
'number' data type to which you referred in come into it?

Ken Sheridan
Stafford, England

Whether or not you should store the age as a value in a column in the table
is a question of functional dependence.

[quoted text clipped - 41 lines]

Hi guys

Thanks for your replies so far

For the form purposes, I calculate age 'now' and age as at 1/1/this
year, neither of them are database fileds as such, just useful info
for the person using the form, *if* the optional date of birth happens
to be entered on the table for that person.

the function is as follows (which I probably copied from the web)

Function Age(varDOB As Variant, Optional varAsOf As Variant) As
Variant
   'Purpose:   Return the Age in years.
   'Arguments: varDOB = Date Of Birth
   '           varAsOf = the date to calculate the age at, or today
if missing.
   'Return:    Whole number of years.
   Dim dtDOB As Date
   Dim dtAsOf As Date
   Dim dtBDay As Date  'Birthday in the year of calculation.
   Age = Null          'Initialize to Null

   'Validate parameters
   If IsDate(varDOB) Then
       dtDOB = varDOB

       If Not IsDate(varAsOf) Then  'Date to calculate age from.
           dtAsOf = Date
       Else
           dtAsOf = varAsOf
       End If

       If dtAsOf >= dtDOB Then      'Calculate only if it's after
person was born.
           dtBDay = DateSerial(Year(dtAsOf), Month(dtDOB), Day
(dtDOB))
           Age = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
       End If
   End If
End Function

Hope the extra info helps...

hmm, you are making me rethink something, thanks for your help so far.
will get back to you after I've though it through.