boblarson
Smeghead
- Local time
- Yesterday, 22:01
- Joined
- Jan 12, 2001
- Messages
- 32,059
I have this code that I am trying to modify (I didn't write the original) and it is basically a very long SQL String which is broken up into the parts -
Select Statement
From Clause
Where Clause
And I modified the SQL String within the QBE grid and pulled out what I THOUGHT was going to work. But I get a "JOIN NOT SUPPORTED" error. Can anyone spot what I might need to modify to work? I know it might be just a little thing that, if I can get this working, will make my day.
The existing code which works fine:
And then my modification which is supposed to be used if a set of combo boxes are used:
This is the full working SQL statement that comes out (using debug):
And the NON-working code that comes out:
Select Statement
From Clause
Where Clause
And I modified the SQL String within the QBE grid and pulled out what I THOUGHT was going to work. But I get a "JOIN NOT SUPPORTED" error. Can anyone spot what I might need to modify to work? I know it might be just a little thing that, if I can get this working, will make my day.
The existing code which works fine:
Code:
strFromClause = "FROM (((((dbo_pselpropertynarrow INNER JOIN dbo_wselIncomeStmtCF ON " & _
"dbo_pselpropertynarrow.property_id = dbo_wselIncomeStmtCF.property_id) INNER JOIN " & _
"dbo_pselOccupancy ON dbo_pselpropertynarrow.property_id = dbo_pselOccupancy." & _
"Property_Id) INNER JOIN dbo_LUStates ON dbo_pselpropertynarrow.state_code = " & _
"dbo_LUStates.StateID) INNER JOIN dbo_LURegions ON dbo_LUStates.Region = " & _
"dbo_LURegions.RegID) LEFT JOIN dbo_dselMainIntRep ON dbo_pselpropertynarrow." & _
"property_id = dbo_dselMainIntRep.PropertyID) INNER JOIN qryXRefCurrentProperties ON " & _
"dbo_pselpropertynarrow.property_id = qryXRefCurrentProperties.PropertyFK "
And then my modification which is supposed to be used if a set of combo boxes are used:
Code:
strFromClause = "FROM dbo_TransPropDevTeam INNER JOIN ((((((dbo_pselpropertynarrow INNER JOIN dbo_wselIncomeStmtCF ON " & _
"dbo_pselpropertynarrow.property_id = dbo_wselIncomeStmtCF.property_id) INNER JOIN " & _
"dbo_pselOccupancy ON dbo_pselpropertynarrow.property_id = dbo_pselOccupancy.Property_Id) " & _
"INNER JOIN dbo_LUStates ON dbo_pselpropertynarrow.state_code = dbo_LUStates.StateID) " & _
"INNER JOIN dbo_LURegions ON dbo_LUStates.Region = dbo_LURegions.RegID) " & _
"LEFT JOIN dbo_dselMainIntRep ON dbo_pselpropertynarrow.property_id = dbo_dselMainIntRep.PropertyID) " & _
"INNER JOIN qryXRefCurrentProperties ON dbo_pselpropertynarrow.property_id = qryXRefCurrentProperties.PropertyFK) " & _
"ON dbo_TransPropDevTeam.PropertyFK = dbo_pselpropertynarrow.property_id"
This is the full working SQL statement that comes out (using debug):
Code:
SELECT DISTINCTROW dbo_dselMainIntRep.FullName AS PrimaryContact, dbo_LURegions.RegName AS Region, dbo_pselpropertynarrow.Market_type, dbo_pselpropertynarrow.Tenant_Mix, IIf([Is_FmHA]=-1,'RHS','non-RHS') AS RHS, dbo_pselpropertynarrow.units, dbo_pselpropertynarrow.property_id, dbo_pselpropertynarrow.lt_name, dbo_pselpropertynarrow.Property_name, dbo_pselpropertynarrow.Property_city, dbo_pselpropertynarrow.county, dbo_pselpropertynarrow.state_code, dbo_pselpropertynarrow.AM_Placed_In_Service_Date, dbo_pselpropertynarrow.Property_Type, Cint([rentable_Sq_ft]/[units]) AS AvgUnitSize, Gross_Rental_Income_Actual, Interest_Credit_Sub, Less_Vacancies_Actual, Net_Rental_Income, Other_Income_Actual, total_income, Administrative, Rent_Expense, Administrative_Payroll, Management_Fees_Actual, OP_Maintenance, Maintenance_Payroll, Utilities, Real_Estate_Taxes_Actual, Hard_DCSR_NetRR_Actual, Insurance_Actual, Other_Expenses, OM_Expense_Actual, Net_Operating_Income_Actual, Transfer_to_Rep_Reserve, Transfer_From_Rep_Reserve,
Total_Debt_PMT_Actual, Gross_Rental_Income_FC, Interest_Credit_Sub_FC, Less_Vacancies_FC, Net_Rental_Income_FC, Other_Income_FC, Total_Income_FC, Administrative_FC, Rent_Expense_FC, Administrative_Payroll_FC, Management_Fees_FC, OP_Maintenance_FC, Maintenance_Payroll_FC, Utilities_FC, Real_Estate_Taxes_FC, Hard_DCSR_NetRR_FC, Insurance_FC, Other_Expenses_FC, OM_Expense_FC, Net_Operating_Income_FC, Transfer_to_Rep_Reserve_FC, Transfer_from_rep_reserve_FC, Total_Debt_PMT_FC, dbo_pselOccupancy.December_Phys_occ_Pct, ([January_Phys_occ_Pct]+[February_Phys_occ_Pct]+[March_Phys_occ_Pct]+[April_Phys_occ_Pct]+[May_Phys_occ_Pct]+[June_Phys_occ_Pct]+[July_Phys_occ_Pct]+[August_Phys_occ_Pct]+[September_Phys_occ_Pct]+[October_Phys_occ_Pct]+[November_Phys_occ_Pct]+[December_Phys_occ_Pct])/12 AS YTD_Average, qryXRefCurrentProperties.PortfolioName, 1 AS Grouping, -1 AS Selected INTO temptblAM_130_Data_Larson FROM (((((dbo_pselpropertynarrow INNER JOIN dbo_wselIncomeStmtCF ON dbo_pselpropertynarrow.property_id = dbo_wselIn
comeStmtCF.property_id) INNER JOIN dbo_pselOccupancy ON dbo_pselpropertynarrow.property_id = dbo_pselOccupancy.Property_Id) INNER JOIN dbo_LUStates ON dbo_pselpropertynarrow.state_code = dbo_LUStates.StateID) INNER JOIN dbo_LURegions ON dbo_LUStates.Region = dbo_LURegions.RegID) LEFT JOIN dbo_dselMainIntRep ON dbo_pselpropertynarrow.property_id = dbo_dselMainIntRep.PropertyID) INNER JOIN qryXRefCurrentProperties ON dbo_pselpropertynarrow.property_id = qryXRefCurrentProperties.PropertyFK WHERE (dbo_wselIncomeStmtCF.PeriodQuarter = 4) AND (dbo_wselIncomeStmtCF.PeriodYear = 2009) AND (dbo_pseloccupancy.periodyear = 2009) AND (dbo_pselpropertynarrow.AM_Placed_In_Service_Date Between #1/1/1976# And #8/1/2011#) AND (([January_Phys_occ_Pct]+[February_Phys_occ_Pct]+[March_Phys_occ_Pct]+[April_Phys_occ_Pct]+[May_Phys_occ_Pct]+[June_Phys_occ_Pct]+[July_Phys_occ_Pct]+[August_Phys_occ_Pct]+[September_Phys_occ_Pct]+[October_Phys_occ_Pct]+[November_Phys_occ_Pct]+[December_Phys_occ_Pct])/12 Between 0 And 1) AND (Cint([rent
able_Sq_ft]/[units]) Between 0 And 10742.5) AND (dbo_pselpropertynarrow.units Between 1 And 2614)
And the NON-working code that comes out:
Code:
SELECT DISTINCTROW dbo_dselMainIntRep.FullName AS PrimaryContact, dbo_LURegions.RegName AS Region, dbo_pselpropertynarrow.Market_type, dbo_pselpropertynarrow.Tenant_Mix, IIf([Is_FmHA]=-1,'RHS','non-RHS') AS RHS, dbo_pselpropertynarrow.units, dbo_pselpropertynarrow.property_id, dbo_pselpropertynarrow.lt_name, dbo_pselpropertynarrow.Property_name, dbo_pselpropertynarrow.Property_city, dbo_pselpropertynarrow.county, dbo_pselpropertynarrow.state_code, dbo_pselpropertynarrow.AM_Placed_In_Service_Date, dbo_pselpropertynarrow.Property_Type, Cint([rentable_Sq_ft]/[units]) AS AvgUnitSize, Gross_Rental_Income_Actual, Interest_Credit_Sub, Less_Vacancies_Actual, Net_Rental_Income, Other_Income_Actual, total_income, Administrative, Rent_Expense, Administrative_Payroll, Management_Fees_Actual, OP_Maintenance, Maintenance_Payroll, Utilities, Real_Estate_Taxes_Actual, Hard_DCSR_NetRR_Actual, Insurance_Actual, Other_Expenses, OM_Expense_Actual, Net_Operating_Income_Actual, Transfer_to_Rep_Reserve, Transfer_From_Rep_Reserve,
Total_Debt_PMT_Actual, Gross_Rental_Income_FC, Interest_Credit_Sub_FC, Less_Vacancies_FC, Net_Rental_Income_FC, Other_Income_FC, Total_Income_FC, Administrative_FC, Rent_Expense_FC, Administrative_Payroll_FC, Management_Fees_FC, OP_Maintenance_FC, Maintenance_Payroll_FC, Utilities_FC, Real_Estate_Taxes_FC, Hard_DCSR_NetRR_FC, Insurance_FC, Other_Expenses_FC, OM_Expense_FC, Net_Operating_Income_FC, Transfer_to_Rep_Reserve_FC, Transfer_from_rep_reserve_FC, Total_Debt_PMT_FC, dbo_pselOccupancy.December_Phys_occ_Pct, ([January_Phys_occ_Pct]+[February_Phys_occ_Pct]+[March_Phys_occ_Pct]+[April_Phys_occ_Pct]+[May_Phys_occ_Pct]+[June_Phys_occ_Pct]+[July_Phys_occ_Pct]+[August_Phys_occ_Pct]+[September_Phys_occ_Pct]+[October_Phys_occ_Pct]+[November_Phys_occ_Pct]+[December_Phys_occ_Pct])/12 AS YTD_Average, qryXRefCurrentProperties.PortfolioName, 1 AS Grouping, -1 AS Selected INTO temptblAM_130_Data_Larson FROM dbo_TransPropDevTeam INNER JOIN ((((((dbo_pselpropertynarrow INNER JOIN dbo_wselIncomeStmtCF ON dbo_pselproper
tynarrow.property_id = dbo_wselIncomeStmtCF.property_id) INNER JOIN dbo_pselOccupancy ON dbo_pselpropertynarrow.property_id = dbo_pselOccupancy.Property_Id) INNER JOIN dbo_LUStates ON dbo_pselpropertynarrow.state_code = dbo_LUStates.StateID) INNER JOIN dbo_LURegions ON dbo_LUStates.Region = dbo_LURegions.RegID) LEFT JOIN dbo_dselMainIntRep ON dbo_pselpropertynarrow.property_id = dbo_dselMainIntRep.PropertyID) INNER JOIN qryXRefCurrentProperties ON dbo_pselpropertynarrow.property_id = qryXRefCurrentProperties.PropertyFK) ON dbo_TransPropDevTeam.PropertyFK = dbo_pselpropertynarrow.property_idWHERE (dbo_wselIncomeStmtCF.PeriodQuarter = 4) AND (dbo_wselIncomeStmtCF.PeriodYear = 2009) AND (dbo_pseloccupancy.periodyear = 2009) AND (dbo_pselpropertynarrow.AM_Placed_In_Service_Date Between #1/1/1976# And #8/1/2011#) AND (([January_Phys_occ_Pct]+[February_Phys_occ_Pct]+[March_Phys_occ_Pct]+[April_Phys_occ_Pct]+[May_Phys_occ_Pct]+[June_Phys_occ_Pct]+[July_Phys_occ_Pct]+[August_Phys_occ_Pct]+[September_Phys_occ_Pct]+[O
ctober_Phys_occ_Pct]+[November_Phys_occ_Pct]+[December_Phys_occ_Pct])/12 Between 0 And 1) AND (Cint([rentable_Sq_ft]/[units]) Between 0 And 10742.5) AND (dbo_pselpropertynarrow.units Between 1 And 2614) AND dbo_TransPropDevTeam.DevTeamTypeFK)=7 AND dbo_TransPropDevTeam.DevTeamFK=1246 AND dbo_TransPropDevTeam.DevRepFK)=1158