[AccessD] Join Issue

[Kaup, Chester]

Thank you for the explanation. I can get it from here.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Thursday, May 05, 2016 1:50 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Join Issue

Ah well that's a whole other problem.

Recordsets, dimmed as DAO.Recordset or ADO.Recordset are used in VBA code, not in the SQL of queries.  Then you run a query you need to use the actual tables, or named queries. SQL knows nothing about VBA (with the exception that MS Access's JET DBMS can call out to VBA to get the return value of a function).


So save the SQL text in your variable strSQL as an actual query and then you can write another query that joins it to ConfigMaster (assuming that is a table or query).

Lambert


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

I now get a new message of The Microsoft Access Database engine cannot find the input table or query RS1. RS1 is dimmed as a public recordset (Public RS1 As recordset). I use a set statement with RS1 after creating it. Set RS1 = MyDB.OpenRecordset(strSQL)

Thanks for your assistance.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Thursday, May 05, 2016 1:30 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Join Issue

In your join statements you are using Bang! Instead of dot.

Try...


RS1 INNER JOIN ConfigMaster ON RS1.[Other Patterns Completion is in]= ConfigMaster.PID

And

INNER JOIN RS2 ON ConfigMaster.ChildPID = RS2.Well_API_14 

Lambert

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Thursday, May 05, 2016 1:58 PM
To: Access Developers discussion and problem solving
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

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

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

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