Hi,
I have the following code created with the assistance of Steve Schapel.
When this code was written, the data type of the fields, tblLoan.LoanNo
and tblCommission.LoanNo was Number.
As I dont have to do any calculations on these fields and to solve
another problem associated with importing the data from Excel I have
changed the data type to Text.
But now the code doesn't work, stating there has been a "Data type
mismatch in Criteria Expression".
I'm assuming I need to convert the references to LoanNo to a String but
I'm not sure just where to do that.

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 tblLoan.LoanNo,
tblLoan.LoanStartDate FROM tblLoan" & _
  " INNER JOIN tblCommission ON tblLoan.LoanNo = tblCommission.LoanNo
WHERE tblCommission.CommissionType=1")

  Do Until rstLoans.EOF
    Set rstMissed = dbs.OpenRecordset("SELECT DISTINCT [MonthYear]" & _
                   " FROM tblDate" & _
                   " WHERE [MonthYear] > " &
CLng(rstLoans!LoanStartDate) & _
                   " AND [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

Thanks,

Bob Wickham