Customized DateDiff function in ms access does not work

Question

I have created a custom DateDiff function for my access database. I want it to give a result between two dates as x years y months e.g. 2 years 7 months. The code is given below:

Option Compare Database
Option Explicit

Function MyDateDiff(D1 As Date, D2 As Date) As Variant
    ' D1= Begin Date
    ' D2= Current Date
    Dim M1 As Integer
    Dim M2 As Integer
    Dim Y1 As Integer
    Dim y As String
    Dim mo As String
    
    If D1 = Null Then
        Exit Function
    End If
    
    If D2 = Null Then
        Exit Function
    End If
    
    M1 = DateDiff("m", D1, D2)
    Y1 = Int(M1 / 12)
    M2 = M1 - (Y1 * 12)
    
    If Y1 > 1 Then
        y = "years"
    Else: y = "year"
    End If
    
    If M2 > 1 Then
        mo = "months"
    Else: mo = "month"
    End If
    
    MyDateDiff = Y1 & y & M2 & mo
    
    
End Function

I used it in a query but it shows an error like the following:

I need to know what I am doing wrong here. Thanks in advance.

Answer 1

Cannot compare anything to Null, not even another Null, because Null has nothing to compare. Use IsNull() or in query use Is Null. Review http://allenbrowne.com/casu-12.html. Also, only Variant type variable can hold Null. A variable declared as Date type will have default value of 12:00:00 AM. Consider:

Function MyDateDiff(D1 As Date, D2 As Date) As Variant
    ' D1= Begin Date
    ' D2= Current Date
    Dim M1 As Integer
    Dim M2 As Integer
    Dim Y1 As Integer
    Dim y As String
    Dim m As String
    
    If Year(D1) = 1899 Or Year(D2) = 1899 Then
        MyDateDiff = Null
    Else
        M1 = DateDiff("m", D1, D2)
        Y1 = Int(M1 / 12)
        M2 = M1 - (Y1 * 12)
        y = " year" & IIf(Y1 > 1, "s", "")
        m = " month" & IIf(M2 > 1, "s", "")
        MyDateDiff = Y1 & y & " " & M2 & m
    End If
End Function