Age at Case closure

[Jessica]

Hi,

I have a formula that calculates age in years and months, but i need another
formula that stops calculating the age when a case is close. To be able to
see how old a client was when their case was close. I have the below fields
birth date and age in months and years.

 

[Daryl S]

Jessica -

When you say formula, I assume you mean a function? If not, you can apply
the same reasoning below to your embedded code...

I assume you have a CaseCloseDate available in your data, and that it is
blank until the case is closed. If your function that calculates age accepts
two dates, and you pass in the birthdate and today's date, then it is a
simple change in your call statement to pass in nz([CaseCloseDate],Now())
instead of Now().

If your age function only accepts BirthDate, then you will need to either
create another function or update the current one. You will need to adjust
the function call to pass in the CaseCloseDate as well. Then whithin the
function, if the CaseCloseDate is null, then use today's date to calculate
the age, otherwise, substitute the CaseCloseDate for today's date.

If this is hard to understand, post the code for your function / formula,
and we can help you adjust it.

 

[Jessica]

I have the following fields Age, birthdate and date close. To calculate age
in years and months i have the below function
AgeWithMonth: Age([BirthDate]) & " years, " & AgeMonths([BirthDate]) & "
months". I need the agewithmonth function to stop calculating the age when i
enter a closing date. So that would give me the age of the client when the
case was close.

Jessica -

When you say formula, I assume you mean a function? If not, you can apply
the same reasoning below to your embedded code...

I assume you have a CaseCloseDate available in your data, and that it is
blank until the case is closed. If your function that calculates age accepts
two dates, and you pass in the birthdate and today's date, then it is a
simple change in your call statement to pass in nz([CaseCloseDate],Now())
instead of Now().

If your age function only accepts BirthDate, then you will need to either
create another function or update the current one. You will need to adjust
the function call to pass in the CaseCloseDate as well. Then whithin the
function, if the CaseCloseDate is null, then use today's date to calculate
the age, otherwise, substitute the CaseCloseDate for today's date.

If this is hard to understand, post the code for your function / formula,
and we can help you adjust it.
--
Daryl S

Hi,

I have a formula that calculates age in years and months, but i need another
formula that stops calculating the age when a case is close. To be able to
see how old a client was when their case was close. I have the below fields
birth date and age in months and years.

 

[Daryl S]

Jessica -

From your example, it looks like you have two functions Age and AgeMonths.
Code for these two functions would typically be in a separate module so they
could be used by multiple queries, forms, etc. Can you open your modules and
find these functions? You can use the binoculars or Ctrl-F to help find
them. When you find these, copy/paste the code into your posting, and we
can help create similar ones for the cases when the close date is populated.

--
Daryl S

I have the following fields Age, birthdate and date close. To calculate age
in years and months i have the below function
AgeWithMonth: Age([BirthDate]) & " years, " & AgeMonths([BirthDate]) & "
months". I need the agewithmonth function to stop calculating the age when i
enter a closing date. So that would give me the age of the client when the
case was close.

Jessica -

When you say formula, I assume you mean a function? If not, you can apply
the same reasoning below to your embedded code...

I assume you have a CaseCloseDate available in your data, and that it is
blank until the case is closed. If your function that calculates age accepts
two dates, and you pass in the birthdate and today's date, then it is a
simple change in your call statement to pass in nz([CaseCloseDate],Now())
instead of Now().

If your age function only accepts BirthDate, then you will need to either
create another function or update the current one. You will need to adjust
the function call to pass in the CaseCloseDate as well. Then whithin the
function, if the CaseCloseDate is null, then use today's date to calculate
the age, otherwise, substitute the CaseCloseDate for today's date.

If this is hard to understand, post the code for your function / formula,
and we can help you adjust it.
--
Daryl S

Hi,

I have a formula that calculates age in years and months, but i need another
formula that stops calculating the age when a case is close. To be able to
see how old a client was when their case was close. I have the below fields
birth date and age in months and years.

 

[Jessica]

Ok, i think i"m not explaning what i need well. What i need is an Expression
for a query so when someone enters a date in the closing date field the age
of the person automatically stays at where it is. I want to know how old they
were when the case was close. I have the following fields in my query age,
birthdate, close date. I have the following expression for another field and
something like that is what i want to create, i tried doing it the same but
it only gives me the age in months i need the age in months and years.

Length of Time in Care: IIf([Closing Date] Is Null,DateDiff("M",[Removal
Date],Date()),DateDiff("M",[Removal Date],[Closing Date]))

Jessica:

You can do this with a single function:

Public Function GetAgeWithMonths(varDoB, varClosedate) As String

Dim intYears As Integer, intMonths As Integer
Dim varDateAt As Date

' if no close date then compute age
' at current date, otherwise at close date
varDateAt = Nz(varClosedate, VBA.Date)

If Not IsNull(varDoB) Then
' get difference in years
intYears = DateDiff("yyyy", varDoB, varDateAt)
' get difference in months
intMonths = DateDiff("m", varDoB, _
DateSerial(Year(varDoB), Month(varDateAt), Day(varDateAt)))
' adjust result if date of birth falls later in year
' than date at which age to be calculated
If varDateAt < DateSerial(Year(varDateAt), Month(varDoB), Day(varDoB))
Then
intYears = intYears - 1
intMonths = intMonths + 12
End If
End If

GetAgeWithMonths = intYears & " year" & _
IIf(intYears <> 1, "s", "") & _
" " & intMonths & " month" & _
IIf(intMonths <> 1, "s", "")

End Function

The expression would then be as follows, passing both the date of birth and
the close date into the function:

AgeWithMonth: GetAgeWithMonths([BirthDate], [Date Close])

If Close Date is Null the function will compute the age at the current date,
otherwise at the close date.

Ken Sheridan
Stafford, England

I have the following fields Age, birthdate and date close. To calculate age
in years and months i have the below function
AgeWithMonth: Age([BirthDate]) & " years, " & AgeMonths([BirthDate]) & "
months". I need the agewithmonth function to stop calculating the age when i
enter a closing date. So that would give me the age of the client when the
case was close.

Jessica -

[quoted text clipped - 22 lines]

see how old a client was when their case was close. I have the below fields
birth date and age in months and years.

--
Message posted via AccessMonster.com


.

 

[Jessica]

Where in the queri will i enter that function?

Jessica:

You can do this with a single function:

Public Function GetAgeWithMonths(varDoB, varClosedate) As String

Dim intYears As Integer, intMonths As Integer
Dim varDateAt As Date

' if no close date then compute age
' at current date, otherwise at close date
varDateAt = Nz(varClosedate, VBA.Date)

If Not IsNull(varDoB) Then
' get difference in years
intYears = DateDiff("yyyy", varDoB, varDateAt)
' get difference in months
intMonths = DateDiff("m", varDoB, _
DateSerial(Year(varDoB), Month(varDateAt), Day(varDateAt)))
' adjust result if date of birth falls later in year
' than date at which age to be calculated
If varDateAt < DateSerial(Year(varDateAt), Month(varDoB), Day(varDoB))
Then
intYears = intYears - 1
intMonths = intMonths + 12
End If
End If

GetAgeWithMonths = intYears & " year" & _
IIf(intYears <> 1, "s", "") & _
" " & intMonths & " month" & _
IIf(intMonths <> 1, "s", "")

End Function

The expression would then be as follows, passing both the date of birth and
the close date into the function:

AgeWithMonth: GetAgeWithMonths([BirthDate], [Date Close])

If Close Date is Null the function will compute the age at the current date,
otherwise at the close date.

Ken Sheridan
Stafford, England

I have the following fields Age, birthdate and date close. To calculate age
in years and months i have the below function
AgeWithMonth: Age([BirthDate]) & " years, " & AgeMonths([BirthDate]) & "
months". I need the agewithmonth function to stop calculating the age when i
enter a closing date. So that would give me the age of the client when the
case was close.

Jessica -

[quoted text clipped - 22 lines]

see how old a client was when their case was close. I have the below fields
birth date and age in months and years.

--
Message posted via AccessMonster.com


.

 

[Jessica]

I"m still not clear, becuase i don't have a function and when i enter the
AgeWithMonth: GetAgeWithMonths([BirthDate], [Closing Date]) in the quei i get
a message since i already have a field calll age with months>

You first paste the function into any standard module. If it’s a new module
then save it under a name different from that of the function or any other
object, e.g. as mdlAgeStuff. But as you already seem to have an Age function
you can simply paste the new one into the same module as that.

In the query, in design view, enter the following in the 'field' row of a
blank column:

AgeWithMonth: GetAgeWithMonths([BirthDate], [Closing Date])

You should then find the AgeWithMonth column gives you either the person's
current age (where they have a Null Closing Date), or their age at the
Closing Date (where there is a date in the Closing Date column), which I
think is what you are looking for, right?

Ken Sheridan
Stafford, England

Where in the queri will i enter that function?

Jessica:

[quoted text clipped - 54 lines]

see how old a client was when their case was close. I have the below fields
birth date and age in months and years.

--
Message posted via AccessMonster.com


.