I have been asked to convert the records for an HOA (4,000 homes)to an Access 2003 database. The records were kept up manually in Excel spread sheets. (Believe me I'd rather have started from scratch.) The importing of the data into new tables, normalization, etc. was easy enough and is executing without problems, especially when entering new data. It is the spreadsheet of historical requests for improvements. The requirement is to associate a specific request to the owner of the property when the request was made. In summary here is the problem:
The table [HomeOwners] is fine; fields pertaining to the problem include:
[HOA3ID] (string) that identifies each property (27-001, 27-002, etc.)
[OwnerID] (string) identifies each owner of a property as the property might have been sold 1 or more times.
[Resident] (integer) identifes the sequence of ownership (1=first, 2=second, etc.)
[MovDate] (date) indicates the Move In date for the above.
There are of course other fields related to Billing address, etc.
tblHistory is the data relating to requests for improvements.(>20,000 records).
It has no data corresponding to [OwnerID], [Resident] or[MovDate]. It does have [HOA3ID] data and the date of the request ([ReqDate]). I have added corresponding fields for [OwnerID] and [Resident]. I need to update [OwnerID] and [Resident] fields. I have tried (and obviously without success) to move data from [Homeowners] ([Resident], [MovDate] for each [OwnerID] and compare the [ReqDate] to the [MovDate]'s for each property.
Example:
In [Homeowners] say there were 4 different owners of 27-001:
[HOA3Id] [OwnerID] [Resident] [MovDate]
27-001 27-001-045 1 03/09/1999
27-001 27-001-332 2 05/07/2001
27-001 27-001-447 3 11/10/2003
27-001 27-001-158 4 01/21/2007
In [tblHistory]
Example# [HOA3Id] [OwnerID] [Resident] [ReqDate]
1 27-001 04/20/2000
2 27-001 06/01/2001
3 27-001 09/20/2004
4 27-001 07/15/2007
5 27-001 09/01/2002
6 27-001 08/29/2007
etc.
The attempted function/iteration would compare the [ReqDate] to the [MovDate]. For example #1 [ReqDate] is > [MovDate] for [Resident] 1, but < [MovDate] [Resident] 2 so the [Resident] value in tblHistory = 1; Example #6 [ReqDate] > [Resident] 4, so the [Resident] value in tblHistory = 4, etc.
The code is below. It has been debugged to the point that the SQL is correct and includes a lot of clutter as a means to check things like last Move In Date (datMaxMove),etc. I have tried the Do Until .EOF. The problem is apparently my syntax or selection of the array parameters as IsArray is false.
Function FnUpdateResNmbr(strHOA3 As String, datRequest As Variant) As Double
' To check and update the Resident Number when adding new Homeowner after Save
'strHOA3 is the Property ID and identifies the address
'datRequest is the date of Improvement Request
Dim I As Integer
Dim dbResHO As DAO.Database
Dim rstResHO As DAO.Recordset
Dim strFilter, strSQLResHO As String
Dim dblResident() As Double 'The nth Homwowner (1=First, 2=Second, etc)
Dim datMoveIn() As Date 'The date of the MoveIn for the Resident number
Dim datMaxMove As Date 'The Move In Date of the last/current resident
Dim dblResUpdate As Double
Dim varRecCount As Variant
Dim intArray() As Integer
strFilter = "([HO3ID] = " & Chr(34) & strHOA3 & Chr(34)
datMaxMove = Nz(DMax("[MovDate]", "[HomeOwners]", strFilter), "Date Not Found")
MsgBox "datMaxMove + " & str(datMaxMove)
strSQLResHO = "SELECT [HO3ID], [Resident], [MovDate] FROM HomeOwners WHERE [HO3ID] = " & "'" & strHOA3 & "'"
Set dbResHO = CurrentDb
Set rstResHO = dbResHO.OpenRecordset(strSQLResHO, dbOpenDynaset)
rstResHO.MoveLast
varRecCount = rstResHO.RecordCount
rstResHO.MoveFirst
ReDim intArray(varRecCount)
For I = 1 To Int(varRecCount)
MsgBox "I: " & str(I)
dblResident(I) = rstResHO!Resident(I)
datMoveIn(I) = rstResHO
Next I
For I = 1 To varRecCount
If datRequest >= datMaxMove Then 'Date of request made by current/last Resident
dblResUpdate = dblResident(I) 'The current (last resident) number
ElseIf (datRequest < datMaxMove) And (datRequest >= datMoveIn(I)) Then
dblResUpdate = dblResident(I)
ElseIf (datRequest < datMaxMove) And (datRequest < datMoveIn(I)) Then
'Request made by a previous Resident
End If
Next I
rstResHO.Close
Set dbResHO = Nothing
Set rstResHO = Nothing
FnUpdateResNmbr = dblResUpdate
End Function
Thanks
The table [HomeOwners] is fine; fields pertaining to the problem include:
[HOA3ID] (string) that identifies each property (27-001, 27-002, etc.)
[OwnerID] (string) identifies each owner of a property as the property might have been sold 1 or more times.
[Resident] (integer) identifes the sequence of ownership (1=first, 2=second, etc.)
[MovDate] (date) indicates the Move In date for the above.
There are of course other fields related to Billing address, etc.
tblHistory is the data relating to requests for improvements.(>20,000 records).
It has no data corresponding to [OwnerID], [Resident] or[MovDate]. It does have [HOA3ID] data and the date of the request ([ReqDate]). I have added corresponding fields for [OwnerID] and [Resident]. I need to update [OwnerID] and [Resident] fields. I have tried (and obviously without success) to move data from [Homeowners] ([Resident], [MovDate] for each [OwnerID] and compare the [ReqDate] to the [MovDate]'s for each property.
Example:
In [Homeowners] say there were 4 different owners of 27-001:
[HOA3Id] [OwnerID] [Resident] [MovDate]
27-001 27-001-045 1 03/09/1999
27-001 27-001-332 2 05/07/2001
27-001 27-001-447 3 11/10/2003
27-001 27-001-158 4 01/21/2007
In [tblHistory]
Example# [HOA3Id] [OwnerID] [Resident] [ReqDate]
1 27-001 04/20/2000
2 27-001 06/01/2001
3 27-001 09/20/2004
4 27-001 07/15/2007
5 27-001 09/01/2002
6 27-001 08/29/2007
etc.
The attempted function/iteration would compare the [ReqDate] to the [MovDate]. For example #1 [ReqDate] is > [MovDate] for [Resident] 1, but < [MovDate] [Resident] 2 so the [Resident] value in tblHistory = 1; Example #6 [ReqDate] > [Resident] 4, so the [Resident] value in tblHistory = 4, etc.
The code is below. It has been debugged to the point that the SQL is correct and includes a lot of clutter as a means to check things like last Move In Date (datMaxMove),etc. I have tried the Do Until .EOF. The problem is apparently my syntax or selection of the array parameters as IsArray is false.
Function FnUpdateResNmbr(strHOA3 As String, datRequest As Variant) As Double
' To check and update the Resident Number when adding new Homeowner after Save
'strHOA3 is the Property ID and identifies the address
'datRequest is the date of Improvement Request
Dim I As Integer
Dim dbResHO As DAO.Database
Dim rstResHO As DAO.Recordset
Dim strFilter, strSQLResHO As String
Dim dblResident() As Double 'The nth Homwowner (1=First, 2=Second, etc)
Dim datMoveIn() As Date 'The date of the MoveIn for the Resident number
Dim datMaxMove As Date 'The Move In Date of the last/current resident
Dim dblResUpdate As Double
Dim varRecCount As Variant
Dim intArray() As Integer
strFilter = "([HO3ID] = " & Chr(34) & strHOA3 & Chr(34)
datMaxMove = Nz(DMax("[MovDate]", "[HomeOwners]", strFilter), "Date Not Found")
MsgBox "datMaxMove + " & str(datMaxMove)
strSQLResHO = "SELECT [HO3ID], [Resident], [MovDate] FROM HomeOwners WHERE [HO3ID] = " & "'" & strHOA3 & "'"
Set dbResHO = CurrentDb
Set rstResHO = dbResHO.OpenRecordset(strSQLResHO, dbOpenDynaset)
rstResHO.MoveLast
varRecCount = rstResHO.RecordCount
rstResHO.MoveFirst
ReDim intArray(varRecCount)
For I = 1 To Int(varRecCount)
MsgBox "I: " & str(I)
dblResident(I) = rstResHO!Resident(I)
datMoveIn(I) = rstResHO
Next I
For I = 1 To varRecCount
If datRequest >= datMaxMove Then 'Date of request made by current/last Resident
dblResUpdate = dblResident(I) 'The current (last resident) number
ElseIf (datRequest < datMaxMove) And (datRequest >= datMoveIn(I)) Then
dblResUpdate = dblResident(I)
ElseIf (datRequest < datMaxMove) And (datRequest < datMoveIn(I)) Then
'Request made by a previous Resident
End If
Next I
rstResHO.Close
Set dbResHO = Nothing
Set rstResHO = Nothing
FnUpdateResNmbr = dblResUpdate
End Function
Thanks
