Can you use RecordsetA data to create RecordsetB

[sysr]

(I am using this in an ASP page)

I am getting a recordset in line 10 and 20, but then I want to use the data in that recordset to create another recordset, is this possible?

Also should I be using the SUM function in lines 60 and 70.

(2 Tables: RegionalContracts and Reporting)

10 sqlStringa = "SELECT OrganizationID FROM RegionalContracts WHERE Region=" & "'" & strSelectedRegion & "'"

20 Set ReportRS = Con.Execute (sqlStringa)

(10 and 20 gives me a recordset of OrganizationID's)

30 WHILE NOT ReportRS.EOF

40 sqlStringb = "SELECT * FROM Reporting WHERE OrganizationID =" & ReportRS("OrganizationID&quot& " AND Year=" & strYear & " AND Quarter=" & strQuarter

50 Set Rst = Con.Execute (sqlStringb)

60 strTotalFiles = strTotalFiles + Rst(TotalFiles)
70 strActiveFiles = strActiveFiles + Rst(ActiveFiles)

80 ReportRS.MoveNext
90 WEND

Thanks for any help!

 

[pflangan]

are TotalFiles and ActiveFiles fields or variables. what i'm getting at is that there are no "" around them if they are fields.

other than that, everything seems fine

 

[link9]

I have also found that sometimes, reading a rs variable straight into the SQL statement yields unpredictable results.... I usually will declare another variable, cast the rs variable into that variable, and then use the variable to build my SQL string --

dim someVar
someVar = cint(rs("id&quot)
sql = "SELECT * FROM table WHERE id=" & someVar

Don't ask me why, but I can always count on the above code to provide exactly what I expect it to, whereas I can't if I just use the rs("id&quot in the string assignment.


Paul Prewett

 

[sysr]

Thank you, it was the "", darn things!