?
Solved

Add another JOIN to a sql statement

Posted on 2011-10-19
3
Medium Priority
?
375 Views
Last Modified: 2012-05-12
I have a button on a form that in the click event is points to a sub that builds a sql statement for the data i need. currently the click event is as below but I thought i might be able to add some additional data by editing the first section where it initially builds the sql by adding on more line,
 sql = sql & " AND JOIN tblImpactedTeams ON T_EVC_UE.[Event#] = tblImpactedTeams.[Event#] "
 to what is already there which is
sql = "SELECT T_EVC_UE.*, [SumOfImpact] AS [Customer Impact] "
    sql = sql & "FROM T_EVC_UE LEFT JOIN qryImpactTotal ON T_EVC_UE.[Event#] = qryImpactTotal.[Event#]"

but in adding that line i put a watch on the sql statement and a stop just after it is built and copy it to a blank query and it pops a message saying missing operator. the result is:

SELECT T_EVC_UE.*, [SumOfImpact] AS [Customer Impact] FROM T_EVC_UE LEFT JOIN qryImpactTotal ON T_EVC_UE.[Event#] = qryImpactTotal.[Event#] AND JOIN tblImpactedTeams ON T_EVC_UE.[Event#] = tblImpactedTeams.[Event#] ;

Private Sub Command173_Click()
On Error GoTo Err_Command173_Click

    Dim oApp As Object
    Dim mywhere As String
    Dim mylen As Integer
    Dim sql As String

  '  Set oApp = CreateObject("Excel.Application")
  '  oApp.Visible = True
    'Only XL 97 supports UserControl Property
  '  On Error Resume Next
  '  oApp.UserControl = True
    
    sql = "SELECT T_EVC_UE.*, [SumOfImpact] AS [Customer Impact] "
    sql = sql & "FROM T_EVC_UE LEFT JOIN qryImpactTotal ON T_EVC_UE.[Event#] = qryImpactTotal.[Event#]"
    sql = sql & " AND JOIN tblImpactedTeams ON T_EVC_UE.[Event#] = tblImpactedTeams.[Event#]"
  
    If InStr(1, Me.RecordSource, "WHERE") <> 0 Then
  
        mylen = 0
        mylen = InStr(1, Me.RecordSource, "ORDER") - InStr(1, Me.RecordSource, "WHERE")
        mywhere = Mid(Me.RecordSource, InStr(1, Me.RecordSource, "WHERE"), mylen)
        sql = sql & " " & mywhere & ";"
    Else
        sql = sql & ";"
    End If
    
    Me.sql.Value = sql

    Dim stDocName As String
    Dim stLinkCriteria As String
    
    If InStr(1, sql, "Team") > 0 Then
        sql = "SELECT T_EVC_UE.*, qryImpactTotal.SumOfImpact AS [Customer Impact] "
        sql = sql & "FROM (T_EVC_UE LEFT JOIN qryImpactTotal ON T_EVC_UE.[Event#] = qryImpactTotal.[Event#]) LEFT JOIN tblImpactedTeams ON T_EVC_UE.[Event#] = tblImpactedTeams.[Event#] "
        mylen = 0
        mylen = InStr(1, Me.RecordSource, "ORDER") - InStr(1, Me.RecordSource, "WHERE")
        mywhere = Mid(Me.RecordSource, InStr(1, Me.RecordSource, "WHERE"), mylen)
        sql = sql & " " & mywhere & ";"
        Me.sql.Value = sql
    End If
    
        stDocName = "frmAllInfo"
        DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria
    
Exit_Command173_Click:
    Exit Sub

Err_Command173_Click:
    MsgBox Err.Description
    Resume Exit_Command173_Click
    
End Sub

Open in new window

0
Comment
Question by:jlcannon
  • 2
3 Comments
 
LVL 44

Accepted Solution

by:
GRayL earned 2000 total points
ID: 36996114
Replace the AND at the start of the second line with INNER.
0
 

Author Closing Comment

by:jlcannon
ID: 36996179
thank you
0
 
LVL 44

Expert Comment

by:GRayL
ID: 36996190
Thanks glad to help.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question