Type mismatch error

[skwilliams]

I'm getting a type mismatch error on this section of code.

Any ideas??

Private Sub btnOK_Click()
Dim varItem As Variant
Dim strDate As Date
Dim strCat As String
Dim strOrderNumber As String
Dim strCatCondition As String
Dim strOrderCondition As String
Dim strSQL As String

For Each varItem In Me.lstDate.ItemsSelected
    strDate = strDate & ",#" & Me.lstDate.ItemData(varItem) & "#"
    Next varItem
    
If Len(strDate) = 0 Then
    strDate = "Like #*#"
Else
    strDate = Right(strDate, Len(strDate) - 1)
    strDate = "IN(" & strDate & ")"
End If

For Each varItem In Me.lstCat.ItemsSelected
    strCat = strCat & ",'" & Me.lstCat.ItemData(varItem) & "'"
    Next varItem
    
If Len(strCat) = 0 Then
    strCat = "Like '*'"
Else
    strCat = Right(strCat, Len(strCat) - 1)
    strCat = "IN(" & strCat & ")"
End If

For Each varItem In Me.lstOrderNumber.ItemsSelected
    strOrder = strOrder & ",'" & Me.lstOrderNumber.ItemData(varItem) & "'"
    Next varItem
    
If Len(strOrder) = 0 Then
    strOrder = "Like '*'"
Else
    strOrder = Right(strOrder, Len(strOrder) - 1)
    strOrder = "IN(" & strOrder & ")"
End If

If Me.optAndCat.Value = True Then
    strCatCondition = " AND "
Else
    strCatCondition = " OR "
End If

If Me.OptAndOrder.Value = True Then
    strOrderCondition = " AND "
Else
    strOrderCondition = " OR "
End If

strSQL = "SELECT tblMain.* FROM tblMain " & "WHERE tblMain.[dtDate] " & strDate & strCatCondition & "tblMain.[Cat] " & strCat & strOrderCondition & "tblMain.[OrderNumber] " & strOrderNumber

MsgBox strSQL

End Sub

 

[the_net_2.0]

date vars in vba do not need to be enclosed in # signs. in the context that you're using it here...what line is it?

 

[boblarson]

Yes, please post the line that it is giving you that is a problem.

Second, you can delete these:

If Len(strOrder) = 0 Then
    strOrder = "Like '*'"
Else

You don't need to include ANYTHING about that field if you want them all. Don't include the Like '*' part at all. There is no point. Since you are building this string up, you can just bypass this field (and any others like it that use the same logic) if there is no value for it.

 

[skwilliams]

strDate = "IN(" & strDate & ")"

if I don't select a date

 

[skwilliams]

and

    strDate = strDate & ",#" & Me.lstDate.ItemData(varItem) & "#"

if I select a date

 

[boblarson]

Your commas are in the wrong place and here's how I would redo it so you had fewer variables and such:

    Dim strWhere As String
    Dim strOrderNumber As String
    Dim strCatCondition As String
    Dim strOrderCondition As String
    Dim strSQL As String
    Dim strHold As String

    For Each varItem In Me.lstDate.ItemsSelected
        strHold = strHold & "#" & Format(Me.lstDate.ItemData(varItem), "\mm\/dd\/yyyy") & "#" & ","
    Next varItem
    If Len(strWhere) > 0 Then
        strWhere = Right(strHold, Len(strHold) - 1)
        strWhere = "[tblMain].[dtDate] IN(" & strWhere & ")"
    End If
    If Me.optAndCat.Value = True Then
        strCatCondition = " AND "
    Else
        strCatCondition = " OR "
    End If

    For Each varItem In Me.lstCat.ItemsSelected
        strHold = Chr(34) & Me.lstCat.ItemData(varItem) & Chr(34) & ","
    Next varItem
    If Len(strWhere) > 0 Then
        strHold = Right(strHold, Len(strHold) - 1)
        strWhere = strWhere & strCatCondition & "[tblMain].[Cat] IN(" & strHold & ")"
    End If
    If Me.OptAndOrder.Value = True Then
        strOrderCondition = " AND "
    Else
        strOrderCondition = " OR "
    End If
    For Each varItem In Me.lstOrderNumber.ItemsSelected
        strHold = strHold & Chr(34) & Me.lstOrderNumber.ItemData(varItem) & Chr(34) & ","
    Next varItem
    If Len(strWhere) > 0 Then
        strWhere = Right(strHold, Len(strHold) - 1)
        strWhere = strWhere & strOrderCondition & "[tblMain].[OrderNumber] IN(" & strHold & ")"
    End If
 

    strSQL = "SELECT tblMain.* FROM tblMain " & "WHERE " & strWhere
    MsgBox strSQL
End Sub

 

[skwilliams]

I used your code and got my strSQL message box.

Thanks for your help!

 

[skwilliams]

Any idea how to now get this criteria to filter the query within my form?

My query name is qrySampleExportList.

 

[boblarson]

Any idea how to now get this criteria to filter the query within my form?

My query name is qrySampleExportList.

If qrySampleExportList has the fields you want with tblMain and is what the form is based on you don't need the entire SQL string; just the where clause. If in the same code you could simply use

Me.Filter = strWhere
Me.FilterOn = True