Error 13 - Type Mismatch

  • Thread starter Thread starter Stacey Crowhurst
  • Start date Start date
S

Stacey Crowhurst

Hi. I have the following code which is supposed to allow my multi-select
list box [listBudgetLines] to filter my report [rptBudgetLinesbyCostCode] by
two criteria [budCCPID] and [bcBudgetCodeID]. I am not familar with coding
and tried to modified posted code to fit my needs. I'm not even sure if this
will do what I intend with the mismatch error fixed, anyway. But any help is
greatly appreciated.

Private Sub cmdViewBudgetLinesbyCostCode_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDelim = """"
strDoc = "rptBudgetLinesbyCostCode"


With Me.listBudgetLines
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[budCCPID] IN (" & Left$(strWhere, lngLen) & ")&" And
"&[bcBudgetCodeID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Budget Line(s): " & " " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub

Please let me know if you need more info. MILLIONS OF THANKS!
Stacey
 
I'm not sure. How do I find out?

Ken Snell MVP said:
On which code step line does the error occur?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Stacey Crowhurst said:
Hi. I have the following code which is supposed to allow my multi-select
list box [listBudgetLines] to filter my report [rptBudgetLinesbyCostCode]
by
two criteria [budCCPID] and [bcBudgetCodeID]. I am not familar with
coding
and tried to modified posted code to fit my needs. I'm not even sure if
this
will do what I intend with the mismatch error fixed, anyway. But any help
is
greatly appreciated.

Private Sub cmdViewBudgetLinesbyCostCode_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDelim = """"
strDoc = "rptBudgetLinesbyCostCode"


With Me.listBudgetLines
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) & """,
"
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[budCCPID] IN (" & Left$(strWhere, lngLen) & ")&" And
"&[bcBudgetCodeID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Budget Line(s): " & " " & Left$(strDescrip,
lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub

Please let me know if you need more info. MILLIONS OF THANKS!
Stacey
Click to expand...
Click to expand...
 
When the error occurs, ACCESS shows you a dialog box...one option is Debug.
Click Debug and the Visual Basic Editor window will open; the offending code
line will be highlighted in yellow.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Stacey Crowhurst said:
I'm not sure. How do I find out?

Ken Snell MVP said:
On which code step line does the error occur?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Stacey Crowhurst said:
Hi. I have the following code which is supposed to allow my
multi-select
list box [listBudgetLines] to filter my report
[rptBudgetLinesbyCostCode]
by
two criteria [budCCPID] and [bcBudgetCodeID]. I am not familar with
coding
and tried to modified posted code to fit my needs. I'm not even sure
if
this
will do what I intend with the mismatch error fixed, anyway. But any
help
is
greatly appreciated.

Private Sub cmdViewBudgetLinesbyCostCode_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDelim = """"
strDoc = "rptBudgetLinesbyCostCode"


With Me.listBudgetLines
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) &
""",
"
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[budCCPID] IN (" & Left$(strWhere, lngLen) & ")&"
And
"&[bcBudgetCodeID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Budget Line(s): " & " " & Left$(strDescrip,
lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub

Please let me know if you need more info. MILLIONS OF THANKS!
Stacey
Click to expand...
Click to expand...
Click to expand...
 
Here is the offending line...

strWhere = "[budCCPID] IN (" & Left$(strWhere, lngLen) & ")&" And
"&[bcBudgetCodeID] IN (" & Left$(strWhere, lngLen) & ")"

FYI: budCCPID and bcBudgetCodeID are both text fields.

Ken Snell MVP said:
When the error occurs, ACCESS shows you a dialog box...one option is Debug.
Click Debug and the Visual Basic Editor window will open; the offending code
line will be highlighted in yellow.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Stacey Crowhurst said:
I'm not sure. How do I find out?

Ken Snell MVP said:
On which code step line does the error occur?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message Hi. I have the following code which is supposed to allow my
multi-select
list box [listBudgetLines] to filter my report
[rptBudgetLinesbyCostCode]
by
two criteria [budCCPID] and [bcBudgetCodeID]. I am not familar with
coding
and tried to modified posted code to fit my needs. I'm not even sure
if
this
will do what I intend with the mismatch error fixed, anyway. But any
help
is
greatly appreciated.

Private Sub cmdViewBudgetLinesbyCostCode_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDelim = """"
strDoc = "rptBudgetLinesbyCostCode"


With Me.listBudgetLines
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) &
""",
"
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[budCCPID] IN (" & Left$(strWhere, lngLen) & ")&"
And
"&[bcBudgetCodeID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Budget Line(s): " & " " & Left$(strDescrip,
lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub

Please let me know if you need more info. MILLIONS OF THANKS!
Stacey
Click to expand...
Click to expand...
Click to expand...
 
You have some syntax errors:

strWhere = "[budCCPID] IN (" & Left$(strWhere, lngLen) & ") " & _
"And [bcBudgetCodeID] IN (" & Left$(strWhere, lngLen) & ")"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




Stacey Crowhurst said:
Here is the offending line...

strWhere = "[budCCPID] IN (" & Left$(strWhere, lngLen) & ")&" And
"&[bcBudgetCodeID] IN (" & Left$(strWhere, lngLen) & ")"

FYI: budCCPID and bcBudgetCodeID are both text fields.

Ken Snell MVP said:
When the error occurs, ACCESS shows you a dialog box...one option is
Debug.
Click Debug and the Visual Basic Editor window will open; the offending
code
line will be highlighted in yellow.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Stacey Crowhurst said:
I'm not sure. How do I find out?

:

On which code step line does the error occur?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


in
message Hi. I have the following code which is supposed to allow my
multi-select
list box [listBudgetLines] to filter my report
[rptBudgetLinesbyCostCode]
by
two criteria [budCCPID] and [bcBudgetCodeID]. I am not familar with
coding
and tried to modified posted code to fit my needs. I'm not even
sure
if
this
will do what I intend with the mismatch error fixed, anyway. But
any
help
is
greatly appreciated.

Private Sub cmdViewBudgetLinesbyCostCode_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDelim = """"
strDoc = "rptBudgetLinesbyCostCode"


With Me.listBudgetLines
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem)
&
""",
"
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[budCCPID] IN (" & Left$(strWhere, lngLen) & ")&"
And
"&[bcBudgetCodeID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Budget Line(s): " & " " & Left$(strDescrip,
lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub

Please let me know if you need more info. MILLIONS OF THANKS!
Stacey
Click to expand...
Click to expand...
Click to expand...
 
Back
Top