Calculated Queries & VBA functions

[Conan Kelly]

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

 

[Douglas J. Steele]

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

 

[Gary Walter]

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]

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

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())

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 Walter]

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

 

[Conan Kelly]

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

 

[Conan Kelly]

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]

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

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

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