Can anyone spot the problem?

[boblarson]

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:

        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:

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

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:

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

 

[boblarson]

The interesting thing is if I add a space at the end, I then get a completely different error message (Extra ) in query expression)

        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_i[COLOR="red"][B]d "[/B][/COLOR]

 

[Banana]

It seems that your first attempt at modifying query had this:

dbo_pselpropertynarrow.property_idWHERE

The new error may indicate that the tables need to be re-()'d. I hate how Access requires () for joins but that's what it is.

Suggest you make use of this SQL Formatter so it'll come out in something more readable and thus easier to verify that you have all ()s in right places.

 

[boblarson]

Well, it turns out (I knew that if I posted the question I'd probably figure it out)

that I had two extra ) in the Where clause I was adding and it had nothing to do with the FROM clause. The FROM needed that space and so with the two parens taken out of the WHERE, it works fine. <whew!!!>

It was in the red parts:

    If Len(Me.cboDevTeamType & "") > 0 Then
        strWhereClause = strWhereClause & _
                         " AND dbo_TransPropDevTeam.DevTeamTypeFK[COLOR="Red"][B])[/B][/COLOR]=" & [Forms]![frmAM_130_Criteria]![cboDevTeamType] & _
                         " AND dbo_TransPropDevTeam.DevTeamFK=" & [Forms]![frmAM_130_Criteria]![cboDevTeamCo] & _
                         " AND dbo_TransPropDevTeam.DevRepFK[B][COLOR="red"])[/COLOR][/B]=" & [Forms]![frmAM_130_Criteria]![cboDevTeamRep]
    End If

I hadn't noticed those stray parens.

Thanks for being a sounding board -

 

[boblarson]

It seems that your first attempt at modifying query had this:

dbo_pselpropertynarrow.property_idWHERE

The new error may indicate that the tables need to be re-()'d. I hate how Access requires () for joins but that's what it is.

Suggest you make use of this SQL Formatter so it'll come out in something more readable and thus easier to verify that you have all ()s in right places.

I would normally use the FMS one that we have available to us. But the person who wrote this, split it all up and it is a bear to try to do anything with (let alone get it to a point where you can format it as a SQL String).