[AccessD] Join Issue

[Stuart McLachlan]

Don't think that will work.  The SQL engine doesn't know about the objects such as 
recordsets created in VBA.


On 5 May 2016 at 18:41, Kaup, Chester wrote:

> 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
>