Problem in inner query

SELECT ReviewMain.LoanID AS [Loan ID], ReviewMain.Asofdate AS [As of Date],  
  (SELECT CreditRating FROM  tblReviewScalars AS Review 
   WHERE (Review.AsOfDate = (SELECT Max(AsOfDate) FROM tblReviewScalars 
                             WHERE AsOfDate<#4/19/2011#))
     AND (Review.LoanID=ReviewMain.LoanID)
     AND (Review.Asofdate=ReviewMain.Asofdate)) 
  AS [CreditRatingAt prior AsOfDate], 
FROM tblReportVectors AS ReportMain INNER JOIN
     tblReviewScalars AS ReviewMain ON (ReportMain.LoanID = ReviewMain.LoanID) 
                                 AND (ReportMain.AsOfDate = ReviewMain.AsOfDate)
WHERE (ReviewMain.Asofdate= DateValue(FunAsofdate()))
GROUP BY ReviewMain.LoanID, ReviewMain.Asofdate;

in above query i have outer query & inner query i get answer for both but when i write (R开发者_开发百科eview.LoanID=ReviewMain.LoanID) AND (Review.Asofdate=ReviewMain.Asofdate) this in inner query i get blank value but actul answer is for creditrating is CCC-. is any soloution on inner query .

This subquery:

(SELECT Max(AsOfDate) FROM tblReviewScalars 
 WHERE AsOfDate<#4/19/2011#)

Is returning the max AsOfDate prior to 4/19/2011 for ALL loans, as opposed to the max AsOfDate for the loan in the current row. You need to change the above subquery to this:

(SELECT Max(AsOfDate) FROM tblReviewScalars AS MaxPrior
 WHERE MaxPrior.AsOfDate<#4/19/2011# 
   AND MaxPrior.LoanID=ReviewMain.LoanID)

Also, you need to get rid of the trailing comma on the line AS [CreditRatingAt prior AsOfDate], so that you have syntactically correct SQL.