• Status: Solved
• Priority: Medium
• Security: Public
• Views: 523

# If Date is Null Then Date = Date()

I have found a function that functions almost to my requirements and is used to show the years of service for an employee. The problem is that it requires a start date and end date. (Both fields on a form) Therefore if an employee is still in service then there is no enddate, so therefore if the enddate is null I would like it to take todays Date() as the enddate

The form also contains a field with the expression

=YMD_DateDiff([StartDate],[EndDate])

The function behind the form is

Function YMD_DateDiff(ByVal pdStDate As Date, _
ByVal pdEndDate As Date) As String
Dim iYears As Integer, iMonths As Integer, iDays As Integer
Dim sDifference As String

iYears = DateDiff("yyyy", pdStDate, pdEndDate)
'dates were less than one year apart, so subtract 1
iYears = iYears - 1
End If

'dates were less than one month apart, so subtract 1
iMonths = iMonths - 1
End If

sDifference = iYears & IIf(iYears = 1, " year, ", " years, ") _
& iMonths & IIf(iMonths = 1, " month, ", " months, ") & iDays _
& IIf(iDays = 1, " day", " days")

YMD_DateDiff = sDifference

End Function

I suspect that it is in the field expression that I should make the ajustment but unsure about how best to achieve it?
0
MGardner
• 2
1 Solution

Commented:
Try this for the calling expression

= ymd_datediff( startdate, nz(enddate, date()))
0

Commented:
I would suggest to set the parameter date, if NULL, to todays date right at the beginning of the function as:
``````IF isNULL(pdEndDate) then
pdEndDate=Date()
end if
``````
0

Author Commented:
excellent solution, many thanks
0

Author Commented:
I tried testing for a null date in the in the function exactly as above but got a Error# but thanks any ways much appreciated
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.