JOIN Sytax problem in Combo Box

[nobull613]

I'm trying to limit the results in one combo box based on the selection in another. After a parameter prompt kept appearing when done via a select query, I've decided to try and update the list based on changing the rowsource on enter.

I've tried the following and get a "Syntax error in JOIN operation" error. Any help with the syntax would be appreciated. Here is what I've tried thus far:

Private Sub Interment_Plot_Enter()

Me.Interment_Plot.RowSource = "SELECT DISTINCT mtbl_Plots.Plot_ID" _
& " FROM (mtbl.Plots INNER JOIN mtbl.Area ON mtbl_Plots.Area_Short = mtbl_Area.Area_Short)" _
& " LEFT JOIN qry_Used_Plots ON mtbl_Plots.Plot_ID = qry_Used_Plots.Taken" _
& " WHERE mtbl_Area.OA_Area = " & Me.OA_Area_LU & " ORDER BY iif(qry_Used_Plots.Taken is null,0,1), mtbl_Plots.Plot_ID"

End Sub

The left joined query is pulling those plots that are used to show unused ones first for selecting.


Thank you for your help.

 

[Remou]

You seem to have used a dot instead of an underscore:

Me.Interment_Plot.RowSource = "SELECT DISTINCT mtbl_Plots.Plot_ID" _
    & " FROM (mtbl[red]_[/red]Plots INNER JOIN mtbl[red]_[/red]Area ON mtbl_Plots.Area_Short = mtbl_Area.Area_Short)" _
    & " LEFT JOIN qry_Used_Plots ON mtbl_Plots.Plot_ID = qry_Used_Plots.Taken" _
    & " WHERE mtbl_Area.OA_Area = " & Me.OA_Area_LU & " ORDER BY iif(qry_Used_Plots.Taken is null,0,1), mtbl_Plots.Plot_ID"

 

[nobull613]

Thank you for the catch. That fixed it. I had to remove the DISTINCT because the order by didn't like it, but otherwise the underscores fixed the syntax error.

Thank you very much.