Getting a "Type Mismatch" error

[Peanut2232425]

I need help. If possible could you look at the code below. I keep getting a type mismatch error. I know I am using Boolean b/c of IWhere. The field it is talking about is a text field.

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Report_Open_Err
Dim myDB As Database
Dim repRS As Recordset

Dim SqlCPC As String
Dim IWhere As Integer
Dim DetailTitle As String

SqlCPC = "Select * from CPC "
If Not IsNull(Forms!frmCPCReport.SettlementOfficer) Then
If IWhere Then
DetailTitle = DetailTitle & ", " & Forms!frmCPCReport.SettlementOfficer
SqlCPC = SqlCPC & " and SettlementOfficer like "" & Forms!frmCPCReport.SettlementOfficer & " * """"
Else
DetailTitle = DetailTitle & " For " & Forms!frmCPCReport.SettlementOfficer
SqlCPC = SqlCPC & " where SettlementOfficer like """ & Forms!frmCPCReport.SettlementOfficer & "*"""
IWhere = True
End If
End If

SqlCPC = SqlCPC & ";"

Set myDB = CurrentDb
Set repRS = myDB.OpenRecordset(SqlCPC)
If Not repRS.EOF And Not repRS.BOF Then
Me.RecordSource = SqlCPC
Else
MsgBox "No records to print"
Cancel = True
End If
'If Not Cancel Then DoCmd.Close acForm, "frmCPCReport"
Me!DetailHeader.Caption = Me!DetailHeader.Caption & DetailTitle

Report_Open_Exit:
Exit Sub

Report_Open_Err:
MsgBox Err.Description
Resume Report_Open_Exit

End Sub

 

[JohnLowell]

It looks to me like there is no Where in the first part.
After the If IWhere Then statement you build the SqlCPC with " and..." but there is no "Where " built into the sql.

I think the first SqlCPC = SqlCPC & should be just like the second one because this includes the Where.

Let me know if this helps.

 

[JerichoJ]

Do you know what line is causing the error?

I agree with JohnLowell that your resulting sql statement needs some cleaning up. Also, it appears that you coded this with the intent to have a loop, but there isn't one.

I would modify the error handling to the following to help you identify the exact line of the error. This will most likely give you the ability to solve your problem:

Report_Open_Exit:
Exit Sub

Report_Open_Err:
MsgBox Err.Description
Stop
Resume
Resume Report_Open_Exit

End Sub

By in putting the Stop command, you cause the code window to be brought up immediately after you click "OK" on the error message. Then, you can press "F8" to move to the next line. Resume tells the error handler to go back to the offending line of code, so press "F8" again to make it do this. Now, you are put on the line that is causing the error. You will probably be able to figure out your problem from there.

Be sure to remove the Stop & Resume from the code before distribution. They are for debugging use only.