Error#13 Type Mismatch

[Nistin27]

I am seeing "Error#13 Type Mismatch". every time a execute the program.

Option Compare Database
Option Explicit

Private Sub cmbUseColorListBox_Click()
UseColorListBox
End Sub

Sub UseColorListBox()

Dim varItem1 As Variant
Dim varItem2 As Variant
Dim strColor As String
Dim strPriority As String
' Dim Criteria As String
'Clear criteria box
' Criteria = ""


For Each varItem1 In [Forms]![Display/Edit Records].Color_Combo.ItemsSelected


strColor = strColor & " Or '" & [Forms]![Display/Edit Records].Color_Combo.ItemData(varItem1) & "'"
Next varItem1

If Len(strColor) = 0 Then
strColor = "Like '*'"
Else
strColor = Right(strColor, Len(strColor) - 4)
strColor = "In(" & strColor & ")"

End If


For Each varItem2 In [Forms]![Display/Edit Records].Priority_Combo.ItemsSelected
strPriority = strPriority & " Or '" & [Forms]![Display/Edit Records].Priority_Combo.ItemData(varItem2) & "'"
Next varItem2

If Len(strPriority) = 0 Then
strPriority = "Like '*'"
Else
strPriority = Right(strPriority, Len(strPriority) - 4)
strPriority = "In(" & strPriority & ")"
End If

'Now prepare to create temporary query
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strNewQuery

Set dbs = CurrentDb
strNewQuery = "TemporaryQuery"
' 'Get rid of query if it already exists, ignore errors
On Error Resume Next
dbs.QueryDefs.Delete strNewQuery

On Error GoTo ErrorHandler

' 'put your select clause here
strSQL = "SELECT CR_List_Details.CR, CR_List_Details.Description, CR_List_Details.Target, CR_List_Details.Color, " & _
"CR_List_Details.Responsible, CR_List_Details.Priority, CR_List_Details.Comment, CR_List_Details.Impact, " & _
"CR_List_Details.[Target Area], CR_List_Details.Team, CR_List_Details.[Gobi Version], CR_List_Details.[G3K Comment]," & _
"CR_List_Details.[Target Team Track], CR_List_Details.[Ram G Track], CR_List_Details.[ASW POCs], " & _
"CR_List_Details.[ETA provided by Tech Team]" & _
"FROM CR_List_Details " & _
"WHERE" & strColor And strPriority & ";"

' Debug.Print "SQL Statement: " & strSQL

Set qdf = dbs.CreateQueryDef(strNewQuery, strSQL)

DoCmd.OpenQuery strNewQuery, acNormal, acEdit

Exit Sub
ErrorHandler:
MsgBox "Error #" & Err.Number & vbCrLf _
& Err.Description
End Sub


Please help me on this.

[pbaldy]

What does the Debug.Print produce?

[ssanfu]

Things I see:

You have the declaration statements. It makes it harder to find/trouble shoot when the "DIM" statements are spread throughout the code. I have all my declarations at the top of the routine.

You are using spaces and punctuation in object names.
--- form name: [Display/Edit Records] (spaces and the "/" character)
--- field names: [ETA provided by Tech Team]
Spaces in names are a pain-in-the-neck!

You are missing spaces in your SQL string. Not so much missing spaces after the commas, but you for sure need a space before the "FROM" clause.

I think your error is being caused because of the "WHERE" clause. This is what you have:

"WHERE" & strColor And strPriority & ";"

Try this:

" WHERE" & strColor & " And " & strPriority & ";"

So far as I can tell from your code, I would think that the WHERE clause would look more like:

"WHERE Me.TxtColor " & strColor & " AND Me.txtPriority "  & strPriority & ";"

ie, "something" that is in strColor and "something" that is in strPriority

-------------------
As an aside, this is how I write my SQL strings. The spaces are at the front so they line up - no spaces at the end:

   strSQL = "SELECT CR_List_Details.CR, CR_List_Details.Description, CR_List_Details.Target, CR_List_Details.Color, "
   strSQL = strSQL & " CR_List_Details.Responsible, CR_List_Details.Priority, CR_List_Details.Comment, CR_List_Details.Impact,"
   strSQL = strSQL & " CR_List_Details.[Target Area], CR_List_Details.Team, CR_List_Details.[Gobi Version], CR_List_Details.[G3K Comment],"
   strSQL = strSQL & " CR_List_Details.[Target Team Track], CR_List_Details.[Ram G Track],"
   strSQL = strSQL & "  CR_List_Details.[ASW POCs], CR_List_Details.[ETA provided by Tech Team]"
   strSQL = strSQL & " FROM CR_List_Details"
   strSQL = strSQL & " WHERE" & strColor & " And " & strPriority & ";"

You can easily see missing or added spaces (the line above the FROM clause)

[rpeare]

The error you're reporting typically happens when you try to link two tables that have mismatching data types (linking a number field to a text field) or when you try to perform a mathematical function on a text field or vice versa. Can you post the SQL statement that is being produced by your debug.print that may help.

[Nistin27]

Let me more clarify the problem,

Private Sub cmbUseColorListBox_Click()
UseColorListBox
End Sub

Sub UseColorListBox()

Dim varItem1 As Variant
Dim varItem2 As Variant
Dim strColor As String
Dim strPriority As String

' This is for taking the criteria of selection from Colorlist box.

For Each varItem1 In [Forms]![Display/Edit Records].Color_Combo.ItemsSelected
strColor = strColor & " Or '" & [Forms]![Display/Edit Records].Color_Combo.ItemData(varItem1) & "'"
Next varItem1

If Len(strColor) = 0 Then
strColor = "Like '*'"
Else
strColor = Right(strColor, Len(strColor) - 4)
strColor = "In(" & strColor & ")"

End If

' This is for taking the criteria of selection from Prioritylist box.

For Each varItem2 In [Forms]![Display/Edit Records].Priority_Combo.ItemsSelected
strPriority = strPriority & " Or '" & [Forms]![Display/Edit Records].Priority_Combo.ItemData(varItem2) & "'"
Next varItem2

If Len(strPriority) = 0 Then
strPriority = "Like '*'"
Else
strPriority = Right(strPriority, Len(strPriority) - 4)
strPriority = "In(" & strPriority & ")"
End If

Now I want to club above two criterias(selection from two listboxes) and want to put it in Query???, How can I do it.
frankly speaking I am very poor in VBA, even I am not sure whether below written code is right or wrong. can you guys please help me in this



'Now prepare to create temporary query
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strNewQuery

Set dbs = CurrentDb
strNewQuery = "TemporaryQuery"
' 'Get rid of query if it already exists, ignore errors
On Error Resume Next
dbs.QueryDefs.Delete strNewQuery

On Error GoTo ErrorHandler

'put your select clause here
strSQL = "SELECT CR_List_Details.CR, CR_List_Details.Description, CR_List_Details.Target, CR_List_Details.Color, " & _
"CR_List_Details.Responsible, CR_List_Details.Priority, CR_List_Details.Comment, CR_List_Details.Impact, " & _
"CR_List_Details.[Target Area], CR_List_Details.Team, CR_List_Details.[Gobi Version], CR_List_Details.[G3K Comment]," & _
"CR_List_Details.[Target Team Track], CR_List_Details.[Ram G Track], CR_List_Details.[ASW POCs], " & _
"CR_List_Details.[ETA provided by Tech Team]" & _
"FROM CR_List_Details " & _
"WHERE" & strColor & " And " & strPriority & ";"

' Debug.Print "SQL Statement: " & strSQL

Set qdf = dbs.CreateQueryDef(strNewQuery, strSQL)

DoCmd.OpenQuery strNewQuery, acNormal, acEdit

Exit Sub
ErrorHandler:
MsgBox "Error #" & Err.Number & vbCrLf _
& Err.Description
End Sub

[pbaldy]

Is it possible? Sure. You didn't answer my question, which is a basic step in debugging this type of thing. Could I or somebody else step through the code and figure out the problem without that? Sure, but then you wouldn't learn how to do it in the future. Where would be the fun in that?

[Nistin27]

I am not linking two tables, I ahve only one table which has following fields
CR
Color
Priority
Description
Comment
Now I wanna put filter on basis of selection from second(Color) and Third(Priority) fields.

[ssanfu]

Nistin27,
Have you tried compiling the code? Is there an error?
If no error, have you set a breakpoint and stepped through the code to find the error? If so, what line?

Did you uncomment the Debug.Print line to see what the SQL looked like? You didn't post it after being asked.

We don't know what the values are for "strColor" and "strPriority"so we can't test the code. At this point all we can do is use what you give us.

We want you to learn how to trouble shoot and diagnose code so you can/will (hopefully), at some point, be able to help others......

I see you changed the "FROM" clause, but you are still missing a space at the end of the line above the "FROM" clause.
This is what the SQL will look like:

...CR_List_Details.[ETA provided by Tech Team]FROM CR_List_Details ...

(the three dots means I cut off the text before and after )

---
Please post the results of the debug line.

[boblarson]

Your code was way off and I think it would take quite a bit to explain it all. So I'm just posting the corrected version and putting my corrections in red and hopefully you can see what you did wrong.

Sub UseColorListBox()
    Dim varItem1 As Variant
    Dim varItem2 As Variant
    Dim strColor As String
    Dim strPriority As String
    Dim strWhere As String
 
    ' This is for taking the criteria of selection from Colorlist box.
    For Each varItem1 In [Forms]![Display/Edit Records].Color_Combo.ItemsSelected
        strColor = strColor & Chr(34) & [Forms]![Display/Edit Records].Color_Combo.ItemData(varItem1) & Chr(34) & ","
    Next varItem1
 
    ' if there is a value then we use it, otherwise don't even set the string (don't use * because it isn't necessary)
    If Len(strColor) > 0 Then
        strColor = Left(strColor, Len(strColor) - 1)
        strColor = " [Color] In(" & strColor & ")"
    End If
 
    ' This is for taking the criteria of selection from Prioritylist box.
    For Each varItem2 In [Forms]![Display/Edit Records].Priority_Combo.ItemsSelected
        strPriority = strPriority & Chr(34) & [Forms]![Display/Edit Records].Priority_Combo.ItemData(varItem2) & Chr(34) & ","
    Next varItem2
 
    If Len(strPriority) > 0 Then
        strPriority = Left(strPriority, Len(strPriority) - 1)
        strPriority = " [Priority] In(" & strPriority & ")"
    End If
 
    strWhere = "WHERE " & strColor & strPriority
 
    'Now prepare to create temporary query
    Dim dbs    As DAO.Database
    Dim qdf    As DAO.QueryDef
    Dim strSQL As String
    Dim strNewQuery
 
    Set dbs = CurrentDb

    strNewQuery = "TemporaryQuery"

    ' 'Get rid of query if it already exists, ignore errors
    On Error Resume Next
    dbs.QueryDefs.Delete strNewQuery
 
    On Error GoTo ErrorHandler
 
    'put your select clause here
    strSQL = "SELECT CR_List_Details.CR, CR_List_Details.Description, CR_List_Details.Target, CR_List_Details.Color, " & _
             "CR_List_Details.Responsible, CR_List_Details.Priority, CR_List_Details.Comment, CR_List_Details.Impact, " & _
             "CR_List_Details.[Target Area], CR_List_Details.Team, CR_List_Details.[Gobi Version], CR_List_Details.[G3K Comment]," & _
             "CR_List_Details.[Target Team Track], CR_List_Details.[Ram G Track], CR_List_Details.[ASW POCs], " & _
             "CR_List_Details.[ETA provided by Tech Team]" & _
             "FROM CR_List_Details " & strWhere

    ' you don't need the semi-colon at the end
 
    ' Debug.Print "SQL Statement: " & strSQL
    Set qdf = dbs.CreateQueryDef(strNewQuery, strSQL)
    dbs.QueryDefs.Append qdf
    DoCmd.openquery strNewQuery, acNormal, acEdit

Exit_UseColorListBox:
    Exit Sub
ErrorHandler:
    MsgBox "Error #" & Err.Number & vbCrLf _
           & Err.Description
    Resume Next
    Resume
End Sub

So, hopefully that helps.