Adding multiple SQL Statment to code

[rlsublime]

I am trying to alter the following code to add this additional criteria:



and where where B_loan_number='" & BLoanNumber & "'"

I am trying to incorporate the BLoan as well but I am getting errors when i try to add it to my sql statements. Any ideas what i am doing wrong here? thanks

 Dim rs As Recordset
    SQL = "select * from investor_request where A_loan_number='" & ALoanNumber & "'"
    Set rs = CurrentDb.OpenRecordset(SQL)
If (rs.RecordCount > 0) Then
    SQL = "Update Investor_Request set Status = 'Closed' where A_Loan_Number ='" & ALoanNumber & "'"
    CurrentDb.Execute SQL, dbFailOnError
End If

[SteveF]

I am trying to alter the following code to add this additional criteria:

and where where B_loan_number='" & BLoanNumber & "'"

I am trying to incorporate the BLoan as well but I am getting errors when i try to add it to my sql statements. Any ideas what i am doing wrong here? thanks

 Dim rs As Recordset
    SQL = "select * from investor_request where A_loan_number='" & ALoanNumber & "'"
    Set rs = CurrentDb.OpenRecordset(SQL)
If (rs.RecordCount > 0) Then
    SQL = "Update Investor_Request set Status = 'Closed' where A_Loan_Number ='" & ALoanNumber & "'"
    CurrentDb.Execute SQL, dbFailOnError
End If

You didn't specify whether or not one or both of the SQL statements should be changed. Here's your code with both statements modified. I also added some more-robust code to the recordset section to deal with the possibility of an empty set -- I've encountered errors when I play with recordsets unless I explicitly check first to make sure they're not empty, and the .BOF/.EOF check is more reliable for me than testing for RecordCount > 0:

Dim rs As DAO.Recordset
    SQL = "select * from investor_request where ((A_loan_number='" & ALoanNumber & "') AND (B_loan_number='" & BLoanNumber & "'));"
   Set rs = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)

With rs
    If not (.BOF = True AND .EOF = True) Then
        SQL = "Update Investor_Request set Status = 'Closed' where ((A_loan_number='" & ALoanNumber & "') AND (B_loan_number='" & BLoanNumber & "'));"
        CurrentDb.Execute SQL, dbFailOnError
    End If
End With

The other important thing is to double-check your criteria for the WHERE clause: Do you want BOTH loan numbers to match, or EITHER one? If it's either, then change the AND between the two loan numbers to OR. Using AND will only build a recordset or perform the update if both numbers match.

Steve