[AccessD] Using recordset in a query

The point is you can't reference a recordset in a SQL query, period.  And if you COULD, you'd have to use a different name for the two recordsets, not the same name.

Charlotte Foust 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Tuesday, April 07, 2009 5:51 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Using recordset in a query

Probably did not explain this very well. RS1 contains the results of a previous SQL statement run in code. I want to use the data stored in RS1 in the query posted below. 

Maybe instead of strSql = I should use strSql1= ?

Thanks.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
Sent: Monday, April 06, 2009 3:41 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Using recordset in a query

You've got it swallowing its own tail!  You're trying to set a recordset to RS1 that refers to fields in RS1, so no, you can't do that.  You can use a subquery and do an inner join on that then open a recordset on the resulting SQL but not like this.

Charlotte Foust 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Monday, April 06, 2009 1:03 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Using recordset in a query

I am trying to use the following query but am getting no records returned. Can this be done or am I approaching this all wrong?

Set RS1 = MyDb.OpenRecordset(strSql)

strSql = "SELECT dbo_DSS_StatusChanges.PID, RS1.Fields(1), " & _ 	"dbo_DSS_StatusChanges.StatusDate, dbo_DSS_StatusChanges.Status " & _
      "FROM RS1 INNER JOIN dbo_DSS_StatusChanges ON " & _
	"(RS1.Fields(3) = dbo_DSS_StatusChanges.StatusDate) AND " & _ 
	"(RS1.Fields(0)  = dbo_DSS_StatusChanges.PID) " & _
      "GROUP BY dbo_DSS_StatusChanges.PID, RS1.Fields(1), " & _ 	"dbo_DSS_StatusChanges.StatusDate, dbo_DSS_StatusChanges.Status;"

Chester Kaup
Engineering Technician
Kinder Morgan CO2 Company, LLP
Office (432) 688-3797
FAX (432) 688-3799

 
No trees were killed in the sending of this message. However a large number of electrons were terribly inconvenienced.


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