Error 13 - Type Mismatch

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
 
S

Stacey Crowhurst

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...
 
K

Ken Snell MVP

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...
 
S

Stacey Crowhurst

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...
 
K

Ken Snell MVP

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Two Multi-Select List Boxes as Filters 11
Multi-Select in List Box 4
Two multi-select list boxes 3
Filter not working 1
Create Report On the Fly 1
Simplify Code-Delete Query 3
Two list boxes not working 2
Open a report based on values from Two List boxes value 2

Top