[AccessD] Using recordset in a query

Hi Chester,

You have two problems:
(1) the RS1.Fields(n) entries are inside quotes so that only the 
"RS1.Fields(n)" text is submitted to the SQL parser, not the 
RS1.Fields(n) *value*. If you modify it like
   strSql = "SELECT dbo_DSS_StatusChanges.PID, " & RS1.Fields(1) & ", " & _
This makes the value in the recordset actually appear in strSql. BTW, if 
you display the contents of strSql in the Immediate window (?strSql) 
you'll see that you currently have the text, not the value.
(2) You are referring to the the RS1 *recordset* in place of a table in 
the query. You can't use recordsets in place of a table. However you can 
get around this by just using the recordset *values*. Try this:

strSql = "SELECT dbo_DSS_StatusChanges.PID, " & RS1.Fields(1) & ", " & _ 	"dbo_DSS_StatusChanges.StatusDate, dbo_DSS_StatusChanges.Status " & _
      "FROM dbo_DSS_StatusChanges " & _
      "WHERE dbo_DSS_StatusChanges.StatusDate = #" & RS1.Fields(3) & "# " & _
	"AND dbo_DSS_StatusChanges.PID = " & RS1.Fields(0) & " " & _
      "GROUP BY dbo_DSS_StatusChanges.PID, " & RS1.Fields(1) & ", " & _ 
               "dbo_DSS_StatusChanges.StatusDate, dbo_DSS_StatusChanges.Status;"
Note: This assumes that StatusDate is actually a *date*, hence the # delimiters
      and that PID is a number (if it's text you'll need single-quote delimeters).

Also, you don't need the "Group By" clause as you aren't using any sum, 
count, etc. You can remove that clause, which will speed the query up a bit.

Hope this helps,
Fred

Kaup, Chester wrote:
> 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
>
>