[AccessD] Using recordset in a query

Hi Chester 

You are mixing up DAO/ADO and SQL. Can't be done.

Write the RS1 recordset to a temp table and use this in your query for RS1. 
Replace the RS1.Fields(n) with the actual field names.

/gustav


>>> Chester_Kaup at kindermorgan.com 07-04-2009 14:50 >>>
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