Can anyone help me with this one?

I have a table in a personal database with Previous & Current Addresses for
we have lived. I'm using that table in a query to calculate the amount of
time living at each address in the form of "x Year(s) y Month(s)".

Having the a "Total Days There" calculated field (using "Move-in Date" and
"Move-out Date" fields for the calculation) in the qurey and passing the
value from that field to the following code, it will calculate the "Time
There" for EVERY record (returns "#Error" for those records missing a "Total
Days There" value because of missing "Move-in Date" or "Move-out Date"
values):

Function CalcTimeThere2(pintDaysThere As Integer) As String
Dim pintYearsThere As Integer
Dim pintMonthsThere As Integer
Dim pintDaysLeft As Integer
Dim pstrYears As String
Dim pstrMonths As String

pintYearsThere = Int(pintDaysThere / 365)
pintDaysLeft = pintDaysThere - (pintYearsThere * 365)
pintMonthsThere = Int(pintDaysLeft / 30)

If pintYearsThere = 0 Then
pstrYears = ""
ElseIf pintYearsThere = 1 Then
pstrYears = "1 Year"
Else
pstrYears = pintYearsThere & " Years"
End If

If pintMonthsThere = 0 Then
pstrMonths = ""
ElseIf pintMonthsThere = 1 Then
pstrMonths = "1 Month"
Else
pstrMonths = " " & pintMonthsThere & " Months"
End If

CalcTimeThere2 = pstrYears & pstrMonths

End Function


The problem is if I try to skip the calculated field in the qurey and pass
the values in the "Move-in Date" and "Move-out Date" fields as two
arguements in the following code and try to do my calculations in code, it
will correctly calculate only the last record correctly. "#Error" is
returned for all of the other fields.

Function CalcTimeThere(pintMoveIn As Integer, pintMoveOut As Integer) As
String
Dim pintYearsThere As Integer
Dim pintMonthsThere As Integer
Dim pintDaysThere As Integer
Dim pintDaysLeft As Integer
Dim pstrYears As String
Dim pstrMonths As String

pintDaysThere = pintMoveOut - pintMoveIn
pintYearsThere = Int(pintDaysThere / 365)
pintDaysLeft = pintDaysThere - (pintYearsThere * 365)
pintMonthsThere = Int(pintDaysLeft / 30)

If pintYearsThere = 0 Then
pstrYears = ""
ElseIf pintYearsThere = 1 Then
pstrYears = "1 Year"
Else
pstrYears = pintYearsThere & " Years"
End If

If pintMonthsThere = 0 Then
pstrMonths = ""
ElseIf pintMonthsThere = 1 Then
pstrMonths = "1 Month"
Else
pstrMonths = " " & pintMonthsThere & " Months"
End If

CalcTimeThere = pstrYears & pstrMonths

End Function

Part of the problem is that the second set of code will not calculate every
field. I'm trying to use the second set of code because I want to be able
to calculate the time at the current address (but there is no "Move-out
Date" for the current address). I know how to do that in code, as long as I
can get the code to calculate every record. If someone knows how to get the
"Move-out Date" for the current address to be the current date every time
the qurey/table is opened, then I can use the first set of code.

Thanks for any help anyone can offer,

Conan Kelly

I notice you've declared the variables as Integer. Are they actually Dates
in the fields, or are you storing something else? And if you don't have a
Move-in Date or a Move-out Date, what's stored? Hopefully it's Null.

If that's the case, try declaring your input parameters as Variants and
checking if their values are Null in your code.

If pintMoveIn is Null, presumably you'll need to set your function output to
Unknown. If pintMoveOut is Null, use the current date instead.

Function CalcTimeThere(pintMoveIn As Variant, _
pintMoveOut As Variant) As String

Dim pintYearsThere As Integer
Dim pintMonthsThere As Integer
Dim pintDaysThere As Integer
Dim pintDaysLeft As Integer
Dim pstrYears As String
Dim pstrMonths As String

If IsNull(pintMoveIn) Then

CalcTimeThere = "Unknown"

Else

If IsNull(pintMoveOut) Then
pintMoveOut = Date()
End If

pintDaysThere = pintMoveOut - pintMoveIn
pintYearsThere = Int(pintDaysThere / 365)
pintDaysLeft = pintDaysThere - (pintYearsThere * 365)
pintMonthsThere = Int(pintDaysLeft / 30)

If pintYearsThere = 0 Then
pstrYears = ""
ElseIf pintYearsThere = 1 Then
pstrYears = "1 Year"
Else
pstrYears = pintYearsThere & " Years"
End If

If pintMonthsThere = 0 Then
pstrMonths = ""
ElseIf pintMonthsThere = 1 Then
pstrMonths = "1 Month"
Else
pstrMonths = " " & pintMonthsThere & " Months"
End If

CalcTimeThere = pstrYears & pstrMonths
End If

End Function

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Conan Kelly" <(E-Mail Removed)> wrote in message
news:vHyyc.6668$(E-Mail Removed)...
> Can anyone help me with this one?
>
> I have a table in a personal database with Previous & Current Addresses
for
> we have lived. I'm using that table in a query to calculate the amount of
> time living at each address in the form of "x Year(s) y Month(s)".
>
> Having the a "Total Days There" calculated field (using "Move-in Date" and
> "Move-out Date" fields for the calculation) in the qurey and passing the
> value from that field to the following code, it will calculate the "Time
> There" for EVERY record (returns "#Error" for those records missing a
"Total
> Days There" value because of missing "Move-in Date" or "Move-out Date"
> values):
>
> Function CalcTimeThere2(pintDaysThere As Integer) As String
> Dim pintYearsThere As Integer
> Dim pintMonthsThere As Integer
> Dim pintDaysLeft As Integer
> Dim pstrYears As String
> Dim pstrMonths As String
>
> pintYearsThere = Int(pintDaysThere / 365)
> pintDaysLeft = pintDaysThere - (pintYearsThere * 365)
> pintMonthsThere = Int(pintDaysLeft / 30)
>
> If pintYearsThere = 0 Then
> pstrYears = ""
> ElseIf pintYearsThere = 1 Then
> pstrYears = "1 Year"
> Else
> pstrYears = pintYearsThere & " Years"
> End If
>
> If pintMonthsThere = 0 Then
> pstrMonths = ""
> ElseIf pintMonthsThere = 1 Then
> pstrMonths = "1 Month"
> Else
> pstrMonths = " " & pintMonthsThere & " Months"
> End If
>
> CalcTimeThere2 = pstrYears & pstrMonths
>
> End Function
>
>
> The problem is if I try to skip the calculated field in the qurey and pass
> the values in the "Move-in Date" and "Move-out Date" fields as two
> arguements in the following code and try to do my calculations in code, it
> will correctly calculate only the last record correctly. "#Error" is
> returned for all of the other fields.
>
> Function CalcTimeThere(pintMoveIn As Integer, pintMoveOut As Integer) As
> String
> Dim pintYearsThere As Integer
> Dim pintMonthsThere As Integer
> Dim pintDaysThere As Integer
> Dim pintDaysLeft As Integer
> Dim pstrYears As String
> Dim pstrMonths As String
>
> pintDaysThere = pintMoveOut - pintMoveIn
> pintYearsThere = Int(pintDaysThere / 365)
> pintDaysLeft = pintDaysThere - (pintYearsThere * 365)
> pintMonthsThere = Int(pintDaysLeft / 30)
>
> If pintYearsThere = 0 Then
> pstrYears = ""
> ElseIf pintYearsThere = 1 Then
> pstrYears = "1 Year"
> Else
> pstrYears = pintYearsThere & " Years"
> End If
>
> If pintMonthsThere = 0 Then
> pstrMonths = ""
> ElseIf pintMonthsThere = 1 Then
> pstrMonths = "1 Month"
> Else
> pstrMonths = " " & pintMonthsThere & " Months"
> End If
>
> CalcTimeThere = pstrYears & pstrMonths
>
> End Function
>
> Part of the problem is that the second set of code will not calculate
every
> field. I'm trying to use the second set of code because I want to be able
> to calculate the time at the current address (but there is no "Move-out
> Date" for the current address). I know how to do that in code, as long as
I
> can get the code to calculate every record. If someone knows how to get
the
> "Move-out Date" for the current address to be the current date every time
> the qurey/table is opened, then I can use the first set of code.
>
> Thanks for any help anyone can offer,
>
> Conan Kelly
>
>

Hi Conan,

[Move-in Date] and [Move-out Date] are
type Date/Time right?

So, how are you using the second function
in your query? My *guess* is that you are
feeding dates to a function that needs integers
for their parameters.

What happens if you change

> Function CalcTimeThere(pintMoveIn As Integer, pintMoveOut As Integer) As
> String

to

Function CalcTimeThere(pintMoveIn , pintMoveOut) As String

and in your query

CalcTimeThere([Move-in Date], NZ([Move-out Date],Date())

"Conan Kelly" wrote
> Can anyone help me with this one?
>
> I have a table in a personal database with Previous & Current Addresses for
> we have lived. I'm using that table in a query to calculate the amount of
> time living at each address in the form of "x Year(s) y Month(s)".
>
> Having the a "Total Days There" calculated field (using "Move-in Date" and
> "Move-out Date" fields for the calculation) in the qurey and passing the
> value from that field to the following code, it will calculate the "Time
> There" for EVERY record (returns "#Error" for those records missing a "Total
> Days There" value because of missing "Move-in Date" or "Move-out Date"
> values):
>
> Function CalcTimeThere2(pintDaysThere As Integer) As String
> Dim pintYearsThere As Integer
> Dim pintMonthsThere As Integer
> Dim pintDaysLeft As Integer
> Dim pstrYears As String
> Dim pstrMonths As String
>
> pintYearsThere = Int(pintDaysThere / 365)
> pintDaysLeft = pintDaysThere - (pintYearsThere * 365)
> pintMonthsThere = Int(pintDaysLeft / 30)
>
> If pintYearsThere = 0 Then
> pstrYears = ""
> ElseIf pintYearsThere = 1 Then
> pstrYears = "1 Year"
> Else
> pstrYears = pintYearsThere & " Years"
> End If
>
> If pintMonthsThere = 0 Then
> pstrMonths = ""
> ElseIf pintMonthsThere = 1 Then
> pstrMonths = "1 Month"
> Else
> pstrMonths = " " & pintMonthsThere & " Months"
> End If
>
> CalcTimeThere2 = pstrYears & pstrMonths
>
> End Function
>
>
> The problem is if I try to skip the calculated field in the qurey and pass
> the values in the "Move-in Date" and "Move-out Date" fields as two
> arguements in the following code and try to do my calculations in code, it
> will correctly calculate only the last record correctly. "#Error" is
> returned for all of the other fields.
>
> Function CalcTimeThere(pintMoveIn As Integer, pintMoveOut As Integer) As
> String
> Dim pintYearsThere As Integer
> Dim pintMonthsThere As Integer
> Dim pintDaysThere As Integer
> Dim pintDaysLeft As Integer
> Dim pstrYears As String
> Dim pstrMonths As String
>
> pintDaysThere = pintMoveOut - pintMoveIn
> pintYearsThere = Int(pintDaysThere / 365)
> pintDaysLeft = pintDaysThere - (pintYearsThere * 365)
> pintMonthsThere = Int(pintDaysLeft / 30)
>
> If pintYearsThere = 0 Then
> pstrYears = ""
> ElseIf pintYearsThere = 1 Then
> pstrYears = "1 Year"
> Else
> pstrYears = pintYearsThere & " Years"
> End If
>
> If pintMonthsThere = 0 Then
> pstrMonths = ""
> ElseIf pintMonthsThere = 1 Then
> pstrMonths = "1 Month"
> Else
> pstrMonths = " " & pintMonthsThere & " Months"
> End If
>
> CalcTimeThere = pstrYears & pstrMonths
>
> End Function
>
> Part of the problem is that the second set of code will not calculate every
> field. I'm trying to use the second set of code because I want to be able
> to calculate the time at the current address (but there is no "Move-out
> Date" for the current address). I know how to do that in code, as long as I
> can get the code to calculate every record. If someone knows how to get the
> "Move-out Date" for the current address to be the current date every time
> the qurey/table is opened, then I can use the first set of code.
>
> Thanks for any help anyone can offer,
>
> Conan Kelly
>
>

Gary & Douglas,

Thank you for the help. [Move-in Date] and [Move-out Date] should be able
to be integer. I tried it like this previously:

Function CalcTimeThere(pintDaysThere As Integer, pintMoveIn As Integer,
pdatMoveIn As Date, pintMoveOut As Integer, pdatMoveOut As Date) As String

and called it from the qurey like this:

CalcTimeThere([Total Days There], [Move-in Date], [Move-in Date],
[Move-out Date], [Move-out Date])

[Total Days There] is my calculated field in the qurey that worked in the
first set of code writen like this:

Total Days There: [Move-out Date]-[Move-in Date]

When I tried to run this qurey in Debug mode (Step Into) using [Move-in
Date] and [Move-out Date] as both an integer and a date, it appears that the
date will come through as both the date serial number in to the integer
variable and as a date into the date variable. So by using them as
integers, it should work.

But the problem is that with the second set of code, it will only calculate
the last record. In the first set of code it will calculate all records.

Thanks again for all the help,

Conan Kelly



"Gary Walter" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Conan,
>
> [Move-in Date] and [Move-out Date] are
> type Date/Time right?
>
> So, how are you using the second function
> in your query? My *guess* is that you are
> feeding dates to a function that needs integers
> for their parameters.
>
> What happens if you change
>
> > Function CalcTimeThere(pintMoveIn As Integer, pintMoveOut As Integer) As
> > String
>
> to
>
> Function CalcTimeThere(pintMoveIn , pintMoveOut) As String
>
> and in your query
>
> CalcTimeThere([Move-in Date], NZ([Move-out Date],Date())
>
> "Conan Kelly" wrote
> > Can anyone help me with this one?
> >
> > I have a table in a personal database with Previous & Current Addresses
for
> > we have lived. I'm using that table in a query to calculate the amount
of
> > time living at each address in the form of "x Year(s) y Month(s)".
> >
> > Having the a "Total Days There" calculated field (using "Move-in Date"
and
> > "Move-out Date" fields for the calculation) in the qurey and passing the
> > value from that field to the following code, it will calculate the "Time
> > There" for EVERY record (returns "#Error" for those records missing a
"Total
> > Days There" value because of missing "Move-in Date" or "Move-out Date"
> > values):
> >
> > Function CalcTimeThere2(pintDaysThere As Integer) As String
> > Dim pintYearsThere As Integer
> > Dim pintMonthsThere As Integer
> > Dim pintDaysLeft As Integer
> > Dim pstrYears As String
> > Dim pstrMonths As String
> >
> > pintYearsThere = Int(pintDaysThere / 365)
> > pintDaysLeft = pintDaysThere - (pintYearsThere * 365)
> > pintMonthsThere = Int(pintDaysLeft / 30)
> >
> > If pintYearsThere = 0 Then
> > pstrYears = ""
> > ElseIf pintYearsThere = 1 Then
> > pstrYears = "1 Year"
> > Else
> > pstrYears = pintYearsThere & " Years"
> > End If
> >
> > If pintMonthsThere = 0 Then
> > pstrMonths = ""
> > ElseIf pintMonthsThere = 1 Then
> > pstrMonths = "1 Month"
> > Else
> > pstrMonths = " " & pintMonthsThere & " Months"
> > End If
> >
> > CalcTimeThere2 = pstrYears & pstrMonths
> >
> > End Function
> >
> >
> > The problem is if I try to skip the calculated field in the qurey and
pass
> > the values in the "Move-in Date" and "Move-out Date" fields as two
> > arguements in the following code and try to do my calculations in code,
it
> > will correctly calculate only the last record correctly. "#Error" is
> > returned for all of the other fields.
> >
> > Function CalcTimeThere(pintMoveIn As Integer, pintMoveOut As Integer) As
> > String
> > Dim pintYearsThere As Integer
> > Dim pintMonthsThere As Integer
> > Dim pintDaysThere As Integer
> > Dim pintDaysLeft As Integer
> > Dim pstrYears As String
> > Dim pstrMonths As String
> >
> > pintDaysThere = pintMoveOut - pintMoveIn
> > pintYearsThere = Int(pintDaysThere / 365)
> > pintDaysLeft = pintDaysThere - (pintYearsThere * 365)
> > pintMonthsThere = Int(pintDaysLeft / 30)
> >
> > If pintYearsThere = 0 Then
> > pstrYears = ""
> > ElseIf pintYearsThere = 1 Then
> > pstrYears = "1 Year"
> > Else
> > pstrYears = pintYearsThere & " Years"
> > End If
> >
> > If pintMonthsThere = 0 Then
> > pstrMonths = ""
> > ElseIf pintMonthsThere = 1 Then
> > pstrMonths = "1 Month"
> > Else
> > pstrMonths = " " & pintMonthsThere & " Months"
> > End If
> >
> > CalcTimeThere = pstrYears & pstrMonths
> >
> > End Function
> >
> > Part of the problem is that the second set of code will not calculate
every
> > field. I'm trying to use the second set of code because I want to be
able
> > to calculate the time at the current address (but there is no "Move-out
> > Date" for the current address). I know how to do that in code, as long
as I
> > can get the code to calculate every record. If someone knows how to get
the
> > "Move-out Date" for the current address to be the current date every
time
> > the qurey/table is opened, then I can use the first set of code.
> >
> > Thanks for any help anyone can offer,
> >
> > Conan Kelly
> >
> >
>
>

Hi Conan,

I set up the simple table ("tblConan")

ID Address Move-in Date Move-out Date
1 first house 2/1/2001 3/31/2002
2 second house 4/1/2002 5/31/2003
3 current house 6/1/2003

I ran the following query

SELECT
tblConan.Address,
CalcTimeThere([Move-in Date],[Move-out Date]) AS TotalDaysThere
FROM tblConan;

using your function where parameters were Integer.

Got #Error's

I put a break on the first line of code in your
function and it never got past the function call
to get to the break!

I changed your function to:

Public Function CalcTimeThere(pMoveIn, pMoveOut) As String
Dim pintYearsThere As Integer
Dim pintMonthsThere As Integer
Dim pintDaysThere As Integer
Dim pintDaysLeft As Integer
Dim pstrYears As String
Dim pstrMonths As String

If Len(Trim(pMoveOut & "")) = 0 Then
pMoveOut = Date
End If

pintDaysThere = pMoveOut - pMoveIn
pintYearsThere = Int(pintDaysThere / 365)
pintDaysLeft = pintDaysThere - (pintYearsThere * 365)
pintMonthsThere = Int(pintDaysLeft / 30)
Debug.Print "MoveIn MoveOut DaysThere YearsThere MonthsThere"
Debug.Print pMoveIn & " " & pMoveOut & " " _
& pintDaysThere & Space(6) & pintYearsThere _
& Space(10) & pintMonthsThere

If pintYearsThere = 0 Then
pstrYears = ""
ElseIf pintYearsThere = 1 Then
pstrYears = "1 Year "
Else
pstrYears = pintYearsThere & " Years "
End If

If pintMonthsThere = 0 Then
pstrMonths = ""
ElseIf pintMonthsThere = 1 Then
pstrMonths = "1 Month"
Else
pstrMonths = " " & pintMonthsThere & " Months"
End If

CalcTimeThere = pstrYears & pstrMonths

End Function


and got following result when reran query:

Address TotalDaysThere
first house 1 Year 1 Month
second house 1 Year 2 Months
current house 1 Year

in Debug Window, I got:

MoveIn MoveOut DaysThere YearsThere MonthsThere
2/1/2001 3/31/2002 423 1 1
MoveIn MoveOut DaysThere YearsThere MonthsThere
4/1/2002 5/31/2003 425 1 2
MoveIn MoveOut DaysThere YearsThere MonthsThere
6/1/2003 6/13/2004 378 1 0

It is up to you whether you want to take the advice or not.

Good luck,

Gary Walter

Thank you, I will try that.

What is throwing me off is that it calculates the "Time There" correctly for
the last record (the last record has both a Move-in Date & a Move-out Date),
but get #Error for all of the others (and that was using Integer variables).
It appeared like the qurey was sending the date serial number to the Integer
variable in the code, so I assumed that it would work.

Thanks again for your help,

Conan


"Gary Walter" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Conan,
>
> I set up the simple table ("tblConan")
>
> ID Address Move-in Date Move-out Date
> 1 first house 2/1/2001 3/31/2002
> 2 second house 4/1/2002 5/31/2003
> 3 current house 6/1/2003
>
> I ran the following query
>
> SELECT
> tblConan.Address,
> CalcTimeThere([Move-in Date],[Move-out Date]) AS TotalDaysThere
> FROM tblConan;
>
> using your function where parameters were Integer.
>
> Got #Error's
>
> I put a break on the first line of code in your
> function and it never got past the function call
> to get to the break!
>
> I changed your function to:
>
> Public Function CalcTimeThere(pMoveIn, pMoveOut) As String
> Dim pintYearsThere As Integer
> Dim pintMonthsThere As Integer
> Dim pintDaysThere As Integer
> Dim pintDaysLeft As Integer
> Dim pstrYears As String
> Dim pstrMonths As String
>
> If Len(Trim(pMoveOut & "")) = 0 Then
> pMoveOut = Date
> End If
>
> pintDaysThere = pMoveOut - pMoveIn
> pintYearsThere = Int(pintDaysThere / 365)
> pintDaysLeft = pintDaysThere - (pintYearsThere * 365)
> pintMonthsThere = Int(pintDaysLeft / 30)
> Debug.Print "MoveIn MoveOut DaysThere YearsThere MonthsThere"
> Debug.Print pMoveIn & " " & pMoveOut & " " _
> & pintDaysThere & Space(6) & pintYearsThere _
> & Space(10) & pintMonthsThere
>
> If pintYearsThere = 0 Then
> pstrYears = ""
> ElseIf pintYearsThere = 1 Then
> pstrYears = "1 Year "
> Else
> pstrYears = pintYearsThere & " Years "
> End If
>
> If pintMonthsThere = 0 Then
> pstrMonths = ""
> ElseIf pintMonthsThere = 1 Then
> pstrMonths = "1 Month"
> Else
> pstrMonths = " " & pintMonthsThere & " Months"
> End If
>
> CalcTimeThere = pstrYears & pstrMonths
>
> End Function
>
>
> and got following result when reran query:
>
> Address TotalDaysThere
> first house 1 Year 1 Month
> second house 1 Year 2 Months
> current house 1 Year
>
> in Debug Window, I got:
>
> MoveIn MoveOut DaysThere YearsThere MonthsThere
> 2/1/2001 3/31/2002 423 1 1
> MoveIn MoveOut DaysThere YearsThere MonthsThere
> 4/1/2002 5/31/2003 425 1 2
> MoveIn MoveOut DaysThere YearsThere MonthsThere
> 6/1/2003 6/13/2004 378 1 0
>
> It is up to you whether you want to take the advice or not.
>
> Good luck,
>
> Gary Walter
>
>
>
>

Gary,

Thanks, that helped. It now works correctly. It seems the main problem
there was that I was explicitly declaring the Move-in & Move-Out Dates as
integer. When it is an integer, it only calculates the last record. When
it has no specific data type assigned, it will calculate all records.

Thanks again for your help. It was very useful.

Conan


"Gary Walter" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Conan,
>
> I set up the simple table ("tblConan")
>
> ID Address Move-in Date Move-out Date
> 1 first house 2/1/2001 3/31/2002
> 2 second house 4/1/2002 5/31/2003
> 3 current house 6/1/2003
>
> I ran the following query
>
> SELECT
> tblConan.Address,
> CalcTimeThere([Move-in Date],[Move-out Date]) AS TotalDaysThere
> FROM tblConan;
>
> using your function where parameters were Integer.
>
> Got #Error's
>
> I put a break on the first line of code in your
> function and it never got past the function call
> to get to the break!
>
> I changed your function to:
>
> Public Function CalcTimeThere(pMoveIn, pMoveOut) As String
> Dim pintYearsThere As Integer
> Dim pintMonthsThere As Integer
> Dim pintDaysThere As Integer
> Dim pintDaysLeft As Integer
> Dim pstrYears As String
> Dim pstrMonths As String
>
> If Len(Trim(pMoveOut & "")) = 0 Then
> pMoveOut = Date
> End If
>
> pintDaysThere = pMoveOut - pMoveIn
> pintYearsThere = Int(pintDaysThere / 365)
> pintDaysLeft = pintDaysThere - (pintYearsThere * 365)
> pintMonthsThere = Int(pintDaysLeft / 30)
> Debug.Print "MoveIn MoveOut DaysThere YearsThere MonthsThere"
> Debug.Print pMoveIn & " " & pMoveOut & " " _
> & pintDaysThere & Space(6) & pintYearsThere _
> & Space(10) & pintMonthsThere
>
> If pintYearsThere = 0 Then
> pstrYears = ""
> ElseIf pintYearsThere = 1 Then
> pstrYears = "1 Year "
> Else
> pstrYears = pintYearsThere & " Years "
> End If
>
> If pintMonthsThere = 0 Then
> pstrMonths = ""
> ElseIf pintMonthsThere = 1 Then
> pstrMonths = "1 Month"
> Else
> pstrMonths = " " & pintMonthsThere & " Months"
> End If
>
> CalcTimeThere = pstrYears & pstrMonths
>
> End Function
>
>
> and got following result when reran query:
>
> Address TotalDaysThere
> first house 1 Year 1 Month
> second house 1 Year 2 Months
> current house 1 Year
>
> in Debug Window, I got:
>
> MoveIn MoveOut DaysThere YearsThere MonthsThere
> 2/1/2001 3/31/2002 423 1 1
> MoveIn MoveOut DaysThere YearsThere MonthsThere
> 4/1/2002 5/31/2003 425 1 2
> MoveIn MoveOut DaysThere YearsThere MonthsThere
> 6/1/2003 6/13/2004 378 1 0
>
> It is up to you whether you want to take the advice or not.
>
> Good luck,
>
> Gary Walter
>
>
>
>

Stupid trivial point, but....

I don't think it "Calculated the last record"

When I set breakpoint, it *never* came
up in the Debug window.

It just did not give #Error because Null
zapped it (for lack of better words) first.

I could be wrong of course.

Gary Walter

"Conan Kelly" wrote
>
> Thanks, that helped. It now works correctly. It seems the main problem
> there was that I was explicitly declaring the Move-in & Move-Out Dates as
> integer. When it is an integer, it only calculates the last record. When
> it has no specific data type assigned, it will calculate all records.
>
> Thanks again for your help. It was very useful.
>
> Conan
>
>
> "Gary Walter" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi Conan,
> >
> > I set up the simple table ("tblConan")
> >
> > ID Address Move-in Date Move-out Date
> > 1 first house 2/1/2001 3/31/2002
> > 2 second house 4/1/2002 5/31/2003
> > 3 current house 6/1/2003
> >
> > I ran the following query
> >
> > SELECT
> > tblConan.Address,
> > CalcTimeThere([Move-in Date],[Move-out Date]) AS TotalDaysThere
> > FROM tblConan;
> >
> > using your function where parameters were Integer.
> >
> > Got #Error's
> >
> > I put a break on the first line of code in your
> > function and it never got past the function call
> > to get to the break!
> >
> > I changed your function to:
> >
> > Public Function CalcTimeThere(pMoveIn, pMoveOut) As String
> > Dim pintYearsThere As Integer
> > Dim pintMonthsThere As Integer
> > Dim pintDaysThere As Integer
> > Dim pintDaysLeft As Integer
> > Dim pstrYears As String
> > Dim pstrMonths As String
> >
> > If Len(Trim(pMoveOut & "")) = 0 Then
> > pMoveOut = Date
> > End If
> >
> > pintDaysThere = pMoveOut - pMoveIn
> > pintYearsThere = Int(pintDaysThere / 365)
> > pintDaysLeft = pintDaysThere - (pintYearsThere * 365)
> > pintMonthsThere = Int(pintDaysLeft / 30)
> > Debug.Print "MoveIn MoveOut DaysThere YearsThere MonthsThere"
> > Debug.Print pMoveIn & " " & pMoveOut & " " _
> > & pintDaysThere & Space(6) & pintYearsThere _
> > & Space(10) & pintMonthsThere
> >
> > If pintYearsThere = 0 Then
> > pstrYears = ""
> > ElseIf pintYearsThere = 1 Then
> > pstrYears = "1 Year "
> > Else
> > pstrYears = pintYearsThere & " Years "
> > End If
> >
> > If pintMonthsThere = 0 Then
> > pstrMonths = ""
> > ElseIf pintMonthsThere = 1 Then
> > pstrMonths = "1 Month"
> > Else
> > pstrMonths = " " & pintMonthsThere & " Months"
> > End If
> >
> > CalcTimeThere = pstrYears & pstrMonths
> >
> > End Function
> >
> >
> > and got following result when reran query:
> >
> > Address TotalDaysThere
> > first house 1 Year 1 Month
> > second house 1 Year 2 Months
> > current house 1 Year
> >
> > in Debug Window, I got:
> >
> > MoveIn MoveOut DaysThere YearsThere MonthsThere
> > 2/1/2001 3/31/2002 423 1 1
> > MoveIn MoveOut DaysThere YearsThere MonthsThere
> > 4/1/2002 5/31/2003 425 1 2
> > MoveIn MoveOut DaysThere YearsThere MonthsThere
> > 6/1/2003 6/13/2004 378 1 0
> >
> > It is up to you whether you want to take the advice or not.
> >
> > Good luck,
> >
> > Gary Walter
> >
> >
> >
> >
>
>