MGardner
asked on
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?
The form also contains a field with the expression
=YMD_DateDiff([StartDate],
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
excellent solution, many thanks
ASKER
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
Open in new window