get additional information from resultset

[Bebe]

I have a stored procedure that returns invoice order numbers that did not seem to get processed.

from those numbers listed in my result set I then want to query out all those invoice order numbers that were cancelled from another table.

could someone give some sample code on how to do this?

so I get a bunch of numbers back from a query and now i want to query on those numbers and make sure none were cancelled???

[Ianpbaker]

Hi Bebe

Once You have have the first recordset try the following


StrSQL = "SELECT * FROM Maintable As Mai, SecondTable As sec WHERE (msi.InvoiceNumber = " & recordset(0)
While Not recordset.EOF
strSql = strSql & " Or msi.InvoiceNumber = " & recordset(0)
recordset.MoveNext
Wend

StrSql = StrSql & ") AND msi.InvoiceNumber = Sec.InvoiceNumber AND sec.Canceled = False"
recordset.Open ....


alternatly you can use this code which does it in one recordset

SELECT .... FROM maintable WHERE ...Your clauses to see if they have been processed... AND invoicenum IN (SELECT invoicenum FROM secondTable WHERE Canceled = False)

Hope This Helps

Ian

[Bebe]

StrSql = StrSql & ") AND msi.InvoiceNumber = Sec.InvoiceNumber AND sec.Canceled = False"
recordset.Open ....


what comes after the open

and why does there not need to be a join on the two tables for this to work. It is two separate tables that we are getting information on?

[Ianpbaker]

Hi Bebe

The First Way that I Did it is the nasty hashed way of doing it using two record sets to solve the problem. You get all the Invoices that haven't been processed from your stored procedure then pass the invoice number from that recordset into the WHERE clause of the second recordset to get the ones that haven't been cancelled. Also, there is a join, it is the "AND msi.InvoiceNumber = Sec.InvoiceNumber"

But I would recommend either ammending your SP or creating a new one to include the nested query that I showed becuase it only uses one record set and Will be a lot quicker to process.

If you need any more questions awnsered, feel free to reply back :D

Hope This Helps

Ian