[AccessD] Join Issue

[McGillivray, Don]

Hi Chester, 

I think your problem is the use of the ! separator between your table and column name in the JOIN clauses.  Looks like maybe you can get away with that in the SELECT clause, but not in the JOIN.

I have no idea whether it's a "best practice" or not, but I never use the ! separator anywhere in SQL.  

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Thursday, May 05, 2016 10:58 AM
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Subject: [AccessD] Join Issue

The following sql throws and error of Join Expression not Supported. I don't think I have a syntax error so am I trying to do the impossible/forbidden? When using actual table and query names it ran fine. Thank you for your assistance.


Function Injectors_around_Completion_in_Activated_Pattern()

    Dim strSQL As String

       strSQL = "SELECT RS1!PatternName, RS1![Completion in Pattern], RS1![Other Patterns Completion is in], ConfigMaster.ChildPID, ConfigMaster.WellName " & _
            "FROM (RS1 INNER JOIN ConfigMaster ON RS1![Other Patterns Completion is in]= ConfigMaster.PID) " & _
            "INNER JOIN RS2 ON ConfigMaster.ChildPID = RS2!Well_API_14 " & _
            "WHERE (((RS2![RBI Cum]) > 0)) " & _
            "GROUP BY RS1!PatternName, RS1![Completion in Pattern], RS1![Other Patterns Completion is in], ConfigMaster.ChildPID, ConfigMaster.WellName " & _
            "ORDER BY RS1!PatternName DESC , RS1![Completion in Pattern], ConfigMaster.ChildPID;"

    Set RS3 = MyDB.OpenRecordset(strSQL)

End Function
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com