Hi,
About a week ago, Steve Schapel kindly helped me with some vba code and
the following is the result.

Private Sub cmdCommissionMissing_Click()
On Error GoTo Err_cmdCommissionMissing_Click

  Dim dbs As Database
  Dim rstLoans As DAO.Recordset
  Dim rstMissed As DAO.Recordset
  Dim strSql As String

  Set dbs = CurrentDb

' call the function DeleteAll("tblMissingCommissionReport")

Call DeleteAll("tblMissingCommissionReport")

  Set rstLoans = dbs.OpenRecordset("SELECT DISTINCT LoanNo FROM
tblCommission")

  Do Until rstLoans.EOF
    Set rstMissed = dbs.OpenRecordset("SELECT DISTINCT [MonthYear]" & _
                   " FROM tblDate" & _
                   " WHERE [MonthYear] < DateSerial(Year(Date()),
Month(Date()), 1)" & _
                   " AND Format([MonthYear],'mmmm,yyyy')" & _
                   " Not In (SELECT Format ([PaymentDate],'mmmm,yyyy')" & _
                              " FROM tblCommission" & _
                              " WHERE LoanNo =" & rstLoans!LoanNo & ")")

     Do Until rstMissed.EOF
          strSql = "INSERT INTO tblMissingCommissionReport (
LoanNumber, TheDate )" & _
          " VALUES ( " & rstLoans!LoanNo & ", " &
CLng(rstMissed!MonthYear) & " )"
          dbs.Execute strSql, dbFailOnError
          rstMissed.MoveNext
     Loop
     rstMissed.Close
     rstLoans.MoveNext
  Loop
  rstLoans.Close
  Set rstMissed = Nothing
  Set rstLoans = Nothing
  Set dbs = Nothing

  ' call the sub RunMcrMissingCommissionReportPrintPreview()

  Call RunMcrMissingCommissionReportPrintPreview

Exit_cmdCommissionMissing_Click:
    Exit Sub

Err_cmdCommissionMissing_Click:
    MsgBox Err.Description
    Resume Exit_cmdCommissionMissing_Click

End Sub

As you can see, I have 2 recordsets, rstLoans and rstMissed.
I now wish to filter rstMissed a little further by adding something like:
" AND [MonthYear] > tblLoan.LoanStartDate" & _
" AND tblCommission.CommissionTypeID = 1" & _

after

" WHERE [MonthYear] < DateSerial(Year(Date()), Month(Date()), 1)" & _

and adding the appropriate tables in the FROM clause.

Even though this will run, it returns incorrect records and is obviously
 the wrong way to go.

Do I need to declare some more recordsets and do this one step at a time.
If so, I am assuming something like :

" AND [rstMissed!MonthYear] > tblLoan.LoanStartDate" & _

OR is it a bit more complex than that and I would be better off with
separate queries written into functions that I could call where needed.

I just need some friendly advice on the best way to go before I spend a
week or two going round in circles (which I have been known to do)

Thanks

Bob Wickham