formulation of triple IIf function 2

[THWatson]

Using Access 2003 in 2000 format

Fields include YearJoined (actually this is a complete date and would more properly be named DateJoined), DateDeceased, DateResigned.

I want to be able to calculate the difference in years between the YearJoined and the Current Date, providing there is no DateDeceased. This gives that to me...

IIf(IsNull([DeceasedDate]),DateDiff("yyyy",[YearJoined],Date())+Int(Format(Date(),"mmdd")<Format([YearJoined],"mmdd")),DateDiff("yyyy",[YearJoined],[DeceasedDate]+Int(Format([DeceasedDate],"mmdd")<Format([YearJoined],"mmdd"))))

I can also get the difference in years between the YearJoined and the Current Date, providing there is not ResignedDate. This gives that to me...

IIf(IsNull([ResignedDate]),DateDiff("yyyy",[YearJoined],Date())+Int(Format(Date(),"mmdd")<Format([YearJoined],"mmdd")),DateDiff("yyyy",[YearJoined],(Date())+Int(Format([ResignedDate],"mmdd")<Format([YearJoined],"mmdd"))))

How do I put those IIf statements together so that...
If there is both no DeceasedDate AND no ResignedDate, I get the years between YearJoined and the Current Date?

Thanks.

Tom

 

[randy700]

This doesn't work?

IIf(IsNull([DeceasedDate]) And IsNull([ResignedDate])

Randy

 

[PHV]

=DateDiff("yyyy",[YearJoined],Nz([DeceasedDate],Nz([ResignedDate],Date())))+Int(Format(Nz([DeceasedDate],Nz([ResignedDate],Date())),"mmdd")<Format([YearJoined],"mmdd"))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[THWatson]

Randy
I actually misstated the problem. If EITHER DeceasedDate or ResignedDate exists, I want the result...they won't ever both be filled in.

PHVNot sure why but that yields a result that is 3 years out.

Tom

 

[THWatson]

PHV
Your statement actually works. I had plugged something in wrong. I apologize!

Thanks.

Tom

 

[THWatson]

I want to expand this a bit. I want to be able to show the length of time a person has been in the club by Years, Months, Weeks and Days. (e.g. 14 years, 3 months, 2 weeks, 3 days). I may lop off the days later after I think about it.

In any event I have a module combination that will give me this, calculating from the data a member joined the club to the current date. The function to use is ShowAge[/b}

Sub CompAge(ByVal varDate1, ByVal varDate2, _
  ByRef lngYears As Long, ByRef lngMonths As Long, _
  ByRef lngWeeks As Long, ByRef lngDays As Long)

  If IsNull(varDate1) Or IsNull(varDate2) Then
    Exit Sub
  End If

  If Not IsDate(varDate1) Or Not IsDate(varDate2) Then
    Exit Sub
  End If

  varDate1 = CDate(varDate1)
  varDate2 = CDate(varDate2)

  If varDate2 < varDate1 Then
    Exit Sub
  End If

  lngYears = Year(varDate2) - Year(varDate1)
  lngMonths = Month(varDate2) - Month(varDate1)
  lngDays = Day(varDate2) - Day(varDate1)
  If lngMonths < 0 Or lngMonths = 0 And lngDays < 0 Then
    lngYears = lngYears - 1
  End If

  varDate1 = DateAdd("yyyy", lngYears, varDate1)
  lngMonths = DateDiff("m", varDate1, varDate2)
  lngDays = Day(varDate2) - Day(varDate1)
  If lngDays < 0 Then
    lngMonths = lngMonths - 1
  End If

  varDate1 = DateAdd("m", lngMonths, varDate1)
  lngDays = varDate2 - varDate1
  lngWeeks = lngDays \ 7
  lngDays = lngDays Mod 7
End Sub

Function Plural(lngNum As Long, strWord As String) As String
  Plural = lngNum & " " & strWord
  If Not lngNum = 1 Then
    Plural = Plural & "s"
  End If
End Function

Function ShowAge(varDOB) As String
  Dim lngYears As Long
  Dim lngMonths As Long
  Dim lngWeeks As Long
  Dim lngDays As Long
  If IsNull(varDOB) Then
    Exit Function
  End If
  If Not IsDate(varDOB) Then
    Exit Function
  End If
  If Date < CDate(varDOB) Then
    Exit Function
  End If
  CompAge CDate(varDOB), Date, lngYears, lngMonths, lngWeeks, lngDays
  If lngYears < 1 Then
  ShowAge = IIf(lngMonths < 1, "", Plural(lngMonths, "Month") & ", ") _
        & IIf(lngWeeks < 1, "", Plural(lngWeeks, "Week")) _
        & IIf(lngDays < 1, "", " and " & Plural(lngDays, "Day"))

  '  ShowAge = Plural(lngMonths, "Month") & ", " & Plural(lngWeeks, "Week") & _
      " and " & Plural(lngDays, "Day")
  ElseIf lngYears < 3 Then
  ShowAge = Plural(lngYears, "Year") & IIf(lngMonths < 1, "", ", " & Plural(lngMonths, "Month")) _
        & IIf(lngWeeks < 1, "", " and " & Plural(lngWeeks, "Week"))
  '  ShowAge = Plural(lngYears, "Year") & ", " & Plural(lngMonths, "Month") & _
      " and " & Plural(lngWeeks, "Week")
  Else
    'ShowAge = Plural(lngYears, "Year") & " and " & Plural(lngMonths, "Month")
    ShowAge = Plural(lngYears, "Year")
  End If
End Function

However, I want the calculation to stop if the member Resigned, in which case there would be a value in the ResignedDate field ... or stop if the member died, in which case there would be a value in the DeceasedDate field.

How do I create a column in a query that uses the ShowAge function but concludes the calculation if there is a value in the ResignedDate or the DeceasedDate column?

Thanks.

Tom

 

[PHV]

Age: IIf(IsNull([DeceasedDate]) And IsNull([ResignedDate]),ShowAge([YearJoined]),'N/A')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[THWatson]

PHV
Thanks for that. I could get that far.

However, I want as well to be able to calculate from the YearJoined to the ResignedDate(if such exists), or from the YearJoined to the DeceasedDate(if such exists) ... and if neither of those exist calculate from the YearJoined to the current Date ... and have only one column to produce the result.

Tom

 

[HarleyQuinn]

You can do this if you change the function to take a second optional date parameter (with the default being "") then if the date is default then set it to Date. Then use that date in the call to CompAge in place of Date (if that makes sense!).

You could then call it something like (untested):

Age: IIf(IsNull([DeceasedDate]) And IsNull([ResignedDate]), ShowAge([YearJoined]), IIf(IsNull([DeceasedDate]), ShowAge([YearJoined], [ResignedDate]), ShowAge([YearJoined], [DeceasedDate])))

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[THWatson]

HarleyQuinn
Thanks. I think I see where you are going with this.

However...
1. It's not clear to me how to change the function.
2. And if I change the ShowAge function to accommodate two dates, then it won't work for the primary purpose of one date. N'est ce pas?

Tom

 

[HarleyQuinn]

If you change the function to use an optional second date it will still work within it's primary purpose.

Here's how I'd change the function to accomodate two dates (I've not sanity checked the second date as is done with the first but that's unnecessary for an example):

Function ShowAge(varDOB, Optional varToDate = "") As String
  Dim lngYears As Long
  Dim lngMonths As Long
  Dim lngWeeks As Long
  Dim lngDays As Long
  If IsNull(varDOB) Then
    Exit Function
  End If
  If Not IsDate(varDOB) Then
    Exit Function
  End If

  [RED]If varToDate = "" then varToDate = Date[/red]
  
  If [red]varToDate[/red]  < CDate(varDOB) Then
    Exit Function
  End If
  CompAge CDate(varDOB), [red]varToDate[/red], lngYears, lngMonths, lngWeeks, lngDays
...

Hope this helps


HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[THWatson]

HarleyQuinn
Well, that seems to work.

Except, I was wondering why some results show in Years, Months, Weeks, Days

Examples:
2 Years, 5 Months
2 Years, 1 Month and 3 Weeks
1 Month, 1 Week and 1 Day
41 Years
3 Years

In fairness, the

Age: IIf(IsNull([DeceasedDate]) And IsNull([ResignedDate]),ShowAge([YearJoined]),'N/A')

from PHV rendered the identical result. And there seems to be no particular rhyme nor reason why.

I'm puzzled.

Tom

 

[HarleyQuinn]

The function will only return a value for a given timespan if it's greater than 0. So if a date is exactly 7 days apart you will get only "1 week" returned.

The function also changes what it outputs based on the number of years between dates. If it's less than a year it outputs months, weeks and days (if > 0) in the output, else if it's less than three years it outputs years, months and weeks (not days anymore) and if it's >=3 years difference then it only outputs years difference.

To remedy this you cna just change the code after the call to CompAge in the ShowAge function to display whatever values you want, personally i'd keep the < 1 branch, do away with the ElseIf branch and then just add months, weeks and days to the Else branch.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[THWatson]

HarleyQuinn
Works like a charm, she do!!

I have never been all that good at putting Modules together, and it's a few years ago that I first constructed that module, and haven't used it in recent times, so had forgotten the innards of it.

I'm still dibbling with it to get it to show exactly what I want...and I think I will probably eliminate the days when result is over 1 year. However, it's getting there.

Thanks for persisting with me in this! I really appreciate it.

Tom

 

[HarleyQuinn]

Glad I could help Tom, thanks for the star

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.