Proper Syntax "Select" "From" and "Inner Joins"

[Massinova]

I currently have an unbound form that I use to search records from 2 tables (Header and Location) in an sql database. A user asked that I include an aditional field from another table (Leases) located in the same database to include in for record search.

listed below is the code that I'm using. I thought I had a good handle on my form until I attempted to add a third table to search from. ...I get no errors, and no results.

Now, I'm really not sure what the proper syntax is for adding a 2nd inner join and 3rd dbo table for selection. (Black is the original code for searching tables ..Highlighted Red are the modifications)

Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String


'Constant Select statement for the RowSource
strSQL = "SELECT dbo.Location.Recno_Location, dbo.Location.ID_Number, dbo.Location.County, dbo.Location.Meridian, dbo.Location.State, dbo.Location.Township, dbo.Location.Township_Dir, dbo.Location.Range, dbo.Location.Range_Dir, dbo.Location.Section,  dbo.Location.Abstract, dbo.Location.Survey, dbo.Header.Type, dbo.Header.Unit_Name, dbo.Header.Serial_Number, dbo.Header.Serial_Page, dbo.Header.Status, dbo.Header.Approved_Date[COLOR=red yellow], dbo.Leases.Lease_Number[/color] " & _
"FROM (dbo.Header INNER JOIN dbo.Location ON dbo.Header.ID_Number = dbo.Location.ID_Number [COLOR=red yellow]INNER JOIN dbo.Leases ON dbo.Header.ID_Number = dbo.Leases.ID_Number[/color])"

strWhere = "WHERE"

strOrder = "ORDER BY dbo.Location.Recno_Location;"


'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.txtAgrmnt) Then '<--If the textbox txtAgrmnt contains no data THEN do nothing
strWhere = strWhere & " (dbo.Location.ID_Number) Like '%" & Me.txtAgrmnt & "%'  AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtCounty) Then
strWhere = strWhere & " (dbo.Location.County) Like '%" & Me.txtCounty & "%'  AND"
End If

If Not IsNull(Me.txtMeridian) Then
strWhere = strWhere & " (dbo.Location.Meridian) Like '%" & Me.txtMeridian & "%'  AND"
End If

If Not IsNull(Me.txtState) Then
strWhere = strWhere & " (dbo.Location.State) Like '%" & Me.txtState & "%'  AND"
End If

If Not IsNull(Me.txtTownship) Then
strWhere = strWhere & " (dbo.Location.Township) Like '%" & Me.txtTownship & "%'  AND"
End If

If Not IsNull(Me.txtTownship_Dir) Then
strWhere = strWhere & " (dbo.Location.Township_Dir) Like '%" & Me.txtTownship_Dir & "%'  AND"
End If

If Not IsNull(Me.txtRange) Then
strWhere = strWhere & " (dbo.Location.Range) Like '%" & Me.txtRange & "%'  AND"
End If

If Not IsNull(Me.txtRange_Dir) Then
strWhere = strWhere & " (dbo.Location.Range_Dir) Like '%" & Me.txtRange_Dir & "%'  AND"
End If

If Not IsNull(Me.txtSection) Then
strWhere = strWhere & " (dbo.Location.Section) Like '%" & Me.txtSection & "%'  AND"
End If

If Not IsNull(Me.txtAbstract) Then
strWhere = strWhere & " (dbo.Location.Abstract) Like '%" & Me.txtAbstract & "%'  AND"
End If

If Not IsNull(Me.txtSurvey) Then
strWhere = strWhere & " (dbo.Location.Survey) Like '%" & Me.txtSurvey & "%'  AND"
End If

If Not IsNull(Me.txtOld_Agreement) Then
strWhere = strWhere & " (dbo.Header.Type) Like '%" & Me.txtOld_Agreement & "%'  AND"
End If

If Not IsNull(Me.txtUnit_Name) Then
strWhere = strWhere & " (dbo.Header.Unit_Name) Like '%" & Me.txtUnit_Name & "%'  AND"
End If

If Not IsNull(Me.txtSerial_Number) Then
strWhere = strWhere & " (dbo.Header.Serial_Number) Like '%" & Me.txtSerial_Number & "%'  AND"
End If

If Not IsNull(Me.txtSerial_Page) Then
strWhere = strWhere & " (dbo.Header.Serial_Page) Like '%" & Me.txtSerial_Page & "%'  AND"
End If

If Not IsNull(Me.txtStatus) Then
strWhere = strWhere & " (dbo.Header.Status) Like '%" & Me.txtStatus & "%'  AND"
End If

If Not IsNull(Me.txtStart_Date) Or Not IsNull(Me.txtEnd_Date) Then
strWhere = strWhere & " Convert(Datetime,dbo.Header.Approved_Date,101) Between '" & Format(Me.txtStart_Date) & "' AND '" & Format(Me.txtEnd_Date) & "'  AND"
End If

[COLOR=red yellow]If Not IsNull(Me.txtLease) Then
strWhere = strWhere & " (dbo.Leases.Lease_Number) Like '%" & Me.txtLease & "%'  AND"
End If[/color]

Debug.Print "strWhere = "; strWhere

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.lstSearchInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

'Column list box on subform specified as 18 columns with widths at 0";1";1";0";0.3";0.3";0.2";0.3";0.2";0.3";0.5";1";1.2";3";0.7";2"

End Sub

 

[cmmrfrds]

If you only want data returned when the condition on the 3rd table is always true, then an inner join is okay.

Otherwise, you will need to make the 3rd table a left join. In that case you will need to explicitly ask for NULL records when the 3rd table condition is not true.

The easiest way to do this is to start your 3 rd table condition with
AND (dbo.Leases.Lease_Number is NULL or (3rd table conditions....))

 

[Massinova]

Listed below is the solution that I found to work.

'Constant Select statement for the RowSource
strSQL = "SELECT dbo.Location.Recno_Location, dbo.Location.ID_Number, dbo.Location.County, dbo.Location.Meridian, dbo.Location.State, dbo.Location.Township, dbo.Location.Township_Dir, dbo.Location.Range, dbo.Location.Range_Dir, dbo.Location.Section, dbo.Location.Abstract, dbo.Location.Survey, dbo.Header.Type, dbo.Header.Unit_Name, dbo.Header.Serial_Number, dbo.Header.Serial_Page, dbo.Header.Status, dbo.Header.Approved_Date, dbo.Leases.Lease_Number " & _
"FROM (dbo.Header INNER JOIN dbo.Location ON dbo.Header.ID_Number = dbo.Location.ID_Number)" & _
"INNER JOIN dbo.Leases ON (dbo.Header.ID_Number = dbo.Leases.ID_Number)"

It boilded down proper placement of parenthesis, quotes, and '&_'

New question what's the purpose/function of '&_'? ...Also, what is the protocol for using parenthesis? ...I ask because my solution was by trial and error