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
Medium Priority
508 Views
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
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
• 2

LVL 61

Accepted Solution

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

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

LVL 5

Expert Comment

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
``````
0

LVL 2

Author Closing Comment

ID: 39235251
excellent solution, many thanks
0

LVL 2

Author Comment

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

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…
###### Suggested Courses
Course of the Month9 days, left to enroll