Broken Module

[Rick Campbell]

I have a broken module. The programmer who created it has disappeared. The
module is supposed to take the last month's data and put it into two tables,
one for homes and one for condos. Instead, it's populating the tables with
data from January. Could someone please take a look at this and tell me
what's broken?

TIA very much!!!!

Option Compare Database

Private iCounter As Integer

Sub Populate_Tables_SFR(strTable As String, strArea As String)
Dim db As DAO.Database
Dim strSQL As String

If strArea = "All" Then
strSales = "a.sales"
Else
strSales = "a." & strArea
End If

Set db = CurrentDb()

strSQL = "insert into tblSFR select top 1 '" & iCounter & "' as seq, '"
& strArea & "' as area,a.sdate,a.average,a.median," & strSales & " as
sales,a.adom,(a.[SP/LP])*100 as [SP/LP],(a.[SP/OLP]*100) as [SP/OLP] from "
& strTable & " a"
' MsgBox strSQL

db.Execute strSQL
End Sub

Sub Populate_Tables_Cond(strTable As String, strArea As String)
Dim db As DAO.Database
Dim strSQL As String

If strArea = "All" Then
strSales = "a.sales"
Else
strSales = "a." & strArea
End If

Set db = CurrentDb()

strSQL = "insert into tblCondos select top 1 '" & iCounter & "' as seq,
'" & strArea & "' as area,a.sdate,a.average,a.median," & strSales & " as
sales,a.adom,(a.[SP/LP])*100 as [SP/LP],(a.[SP/OLP]*100) as [SP/OLP] from "
& strTable & " a"
' MsgBox strSQL

db.Execute strSQL
End Sub

Sub Delete_Table_SFR()
Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb()

strSQL = "delete from tblSFR"

db.Execute strSQL

strSQL = "delete from tblCondos"

db.Execute strSQL
End Sub
Sub Populate_SFR()
Dim intArea As Integer

Call Delete_Table_SFR

iCounter = 0
Call Populate_Tables_SFR("[SFR City Monthly]", "All")

For intArea = 1 To 10
iCounter = iCounter + 1
Call Populate_Tables_SFR("[SFR Monthly Area " & intArea & "]", "D" &
intArea)
Next

iCounter = 0

Call Populate_Tables_Cond("[Condos City Monthly]", "All")

For intArea = 1 To 10
iCounter = iCounter + 1
Call Populate_Tables_Cond("[Condos Monthly Area " & intArea & "]",
"D" & intArea)
Next

End Sub

 

[Alex Dybenko]

Hi,
not sure that one can help, without looking at your tables and understand
data structure. Try to debug your code and check how insert queries are
built, perhaps some problem there

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com