data types

[Bob Wickham]

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

 

[Guest]

Since the LoanNo is a String now you will have to quote values in where
clauses:

Try canging:

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 & ")")

Change last line to:

" WHERE LoanNo = '" & rstLoans!LoanNo & "')")

Please note the use of single quotation marks.
Please let me know if that worked.

Regards,

Dirk Louwers

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

 

[Bob Wickham]

Since the LoanNo is a String now you will have to quote values in where
clauses:

Try canging:

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 & ")")

Change last line to:

" WHERE LoanNo = '" & rstLoans!LoanNo & "')")

Please note the use of single quotation marks.
Please let me know if that worked.

Regards,

Dirk Louwers

Hi Dirk,

Yes, that works.
I actually withdrew my post because I thought I found another way around
it, but I didn't.

You see, I'm linking to an Excel spreadsheet. In that spreadsheet the
LoanNo field is a Number. When I append those records to an Access table
where the LoanNo field is Text it "magically" becomes Text. I dont
understand how it does this and it is the source of some confusion.

But your solution is so simple and most importantly, it works.

Thankyou

Bob Wickham