VBA Date issue (1 Viewer)

Chintsapete · Jan 6, 2017

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.

Code:

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 · Jan 6, 2017

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:

Code:

        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:

Code:

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

Chintsapete · Jan 6, 2017

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

VBA Date issue (1 Viewer)

Chintsapete

Registered User.

stopher

AWF VIP

Chintsapete

Registered User.

Similar threads

Users who are viewing this thread