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

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 dYearsAddedDate As Date, dMonthsAddedDate As Date
Dim sDifference As String

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

iMonths = DateDiff("m", dYearsAddedDate, pdEndDate)
dMonthsAddedDate = DateAdd("m", iMonths, dYearsAddedDate)
If dMonthsAddedDate > pdEndDate Then
  'dates were less than one month apart, so subtract 1
  iMonths = iMonths - 1
  dMonthsAddedDate = DateAdd("m", iMonths, dYearsAddedDate)
End If

iDays = DateDiff("d", dMonthsAddedDate, pdEndDate)
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
Asked:
MGardner
  • 2
1 Solution
 
mbizupCommented:
Try this for the calling expression

 = ymd_datediff( startdate, nz(enddate, date()))
0
 
DOSLoverCommented:
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

Open in new window

0
 
MGardnerAuthor Commented:
excellent solution, many thanks
0
 
MGardnerAuthor 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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now