query calculates the age

[azhar2006]

Hello guys
I have this query that calculates the age of the employee, works fine for a few seconds and then an error message pops up. please help

SELECT tblmastr.ID, tblmastr.StatFig, tblmastr.Rtba, tblmastr.FullName, tblmastr.Department, fnAge([DateBirth],Date()) AS Age
FROM tblmastr
WHERE (((fnAge([DateBirth],Date()))>=59));

 

[theDBguy]

I am guessing there's a problem with your data. Can you tell which record is causing the error?

 

[June7]

What is the error message?

 

[azhar2006]

I am guessing there's a problem with your data. Can you tell which record is causing the error?

Yes, yes, my friend Sherk. There are empty fields in DateBirth I think NZ will solve the problem. Is not this right ?

 

[azhar2006]

What is the error message?

 

[June7]

Use Nz or IIf() with IS NULL or change function to deal with Null passed to it. Would probably have to declare argument as Variable type.

 

[theDBguy]

Yes, yes, my friend Sherk. There are empty fields in DateBirth I think NZ will solve the problem. Is not this right ?

Worth a try...

 

[azhar2006]

Use Nz or IS NULL or change function to deal with Null passed to it. Would probably have to declare argument as Variable type.

Thank you,

theDBguy​

June7​

my friends. Your words are really accurate. I used (Nz) and the problem was solved. A zero was placed in the fields empty of the date of birth​

 

[theDBguy]

Thank you,

theDBguy​

June7​

my friends. Your words are really accurate. I used (Nz) and the problem was solved. A zero was placed in the fields empty of the date of birth​

Glad to hear you got it sorted out. Cheers!

 

[Gasman]

Why not ignore those records, as the calc is going to be wrong, surely?

 

[azhar2006]

Why not ignore those records, as the calc is going to be wrong, surely?

The problem with some employees is that their information is delayed for the purpose of entering it. This is the problem we have, my friend

 

[Gasman]

The problem with some employees is that their information is delayed for the purpose of entering it. This is the problem we have, my friend

But the calculation is still going to be rubbish?

 

[azhar2006]

But the calculation is still going to be rubbish?

How is that . I did not understand ?

 

[Gasman]

Well if you make missing dob as 0, the age is going to be somewhat out, I would have thought?

 

[June7]

Another option is to not call the function if there is no data.

IIf([DateBirth] Is Null, Null, fnAge([DateBirth],Date()))>=59))

 

[arnelgp]

you can also Change your fnAge() function to handle Null:

Function fnAge(dtmBD As Variant, Optional dtmDate As Date = 1) _
 As Variant
    ' Calculate a person's age, given the person's birth date and
    ' an optional "current" date.
    If IsNull(dtmBD) Or Not (IsDate(dtmBD)) Then
        Exit Function
    End If
    If dtmDate = 1 Then
        ' Did the caller pass in a date? If not, use
        ' the current date.
        dtmDate = Date
    End If
    fnAge = DateDiff("yyyy", dtmBD, dtmDate) + _
     (dtmDate < DateSerial(Year(dtmDate), Month(dtmBD), _
      Day(dtmBD)))
End Function

 

[Gasman]

you can also Change your fnAge() function to handle Null:

Function fnAge(dtmBD As Variant, Optional dtmDate As Date = 1) _
As Variant
    ' Calculate a person's age, given the person's birth date and
    ' an optional "current" date.
    If IsNull(dtmBD) Or Not (IsDate(dtmBD)) Then
        Exit Function
    End If
    If dtmDate = 1 Then
        ' Did the caller pass in a date? If not, use
        ' the current date.
        dtmDate = Date
    End If
    fnAge = DateDiff("yyyy", dtmBD, dtmDate) + _
     (dtmDate < DateSerial(Year(dtmDate), Month(dtmBD), _
      Day(dtmBD)))
End Function

@arnelgp
What does the function return then?, when you are trying to compare to a number?

 

[arnelgp]

What does the function return then?

zls?

 

[Gasman]

zls?

It would appear so?, so no good comparing to a number?

 

[arnelgp]

you can use Val(fnAge([field])) = number?