Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

If Date is Null Then Date = Date()

Posted on 2013-06-10
4
Medium Priority
?
508 Views
Last Modified: 2013-06-10
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
Comment
Question by:MGardner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 39235193
Try this for the calling expression

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

Expert Comment

by:DOSLover
ID: 39235202
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
 
LVL 2

Author Closing Comment

by:MGardner
ID: 39235251
excellent solution, many thanks
0
 
LVL 2

Author Comment

by:MGardner
ID: 39235261
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question