VBA Date issue

[Chintsapete]

I'm not quite sure why below doesn't work. I have a form where the [DateOfTermination] is blank until the guys leave the company. Then I'll input the date. Once the date is in it should use [DateOfTermination] as EndDate to determent how many years they worked for us when they quit, else it should use today's date. For some reason if the field is blank it always uses 00:00:00.

Public Function fnYearsEmployed(DateOfEngagment _
                                , DateOfTermination)

        
        Dim YearsEmployed As Integer
        Dim EndDate As Date
        If DateOfTermination Is Not Null Then
            EndDate = DateOfTermination
        Else
            EndDate = Date
        End If
        
        YearsEmployed = Nz(Int((DateDiff("yyyy", DateOfEngagment,    EndDate))))
        fnYearsEmployed = YearsEmployed
        
        
End Function

Thanks for any help
Pete

 

[stopher]

Use IsNull() in VBA to check for null values.

"Is Null" is really for SQL.


As a tip you might want to consider switching your If statement round to avoid testing for negative:

        If IsNull(DateOfTermination) Then
            EndDate = Date
        Else
            EndDate = DateOfTermination
        End If

Avoiding negatives in code like this can make it easier to read.

Also, consider explicitly stating your data types in the function statement like this:

Public Function fnYearsEmployed(DateOfEngagment as Date_
                                , DateOfTermination as Date) as Integer

 

[Chintsapete]

Thanks Stopher, so close and yet so far, I keep mixing the stuff up with SQL and VBA. I don't do much VBA and never find the time to learn more. Story of my life. It works perfect, I appreciate your time to help.
Take care