Multi-select list box query

[kirschey]

I have read the other posts on this subject, but I am still having issues.

I have a few combo, and one multiselect list box for States delimiting. I am trying to feed a query based on this info. I have it working as a combo box, but now I am getting "type mismatch" and "object variable or with block variable not set" errors.

Help would be much appreciated in the "listbox" commented section and the stSQL definition.

Option Compare Database
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim prm As DAO.Parameter
Private Sub Run_Click()
On Error GoTo Err_Run_Click
    Dim SpreadSheetSource As String
    Dim SpreadSheetOutput As String
    Dim stTerritory As String
    Dim stState As String
    Dim stStatePrint As String
    Dim stBrand As String
    Dim dFirstMonth As Date
    Dim stDocName As String
    Dim Suc As Boolean
    Dim varItem As Variant
    Dim listItems As Control
    Dim stSQL As String
    
    'Data definitions
    'SpreadSheetSource = "S:\__2 2009 Budgets\Sales and Marketing expenses\Reports\Profit loss Flow.xls"
    'SpreadSheetOutput = "S:\__2 2009 Budgets\Sales and Marketing expenses\Reports\Profit loss Flow_new.xls"
    SpreadSheetSource = "Y:\Documents\Freelance\Cape Classics\V6\Profit loss Flow.xls"
    SpreadSheetOutput = "Y:\Documents\Freelance\Cape Classics\V6\Profit loss Flow_new.xls"

    
    stDocName = "output"
        dFirstMonth = Me.firstmonth
    
    'list box
    Set listItems = Me.State
    For Each varItem In listItems.ItemsSelected
        If stState > "" Then
            stState = stState & " And (([zz Profit and Loss Flow Final].[State]) Like """ * """ &" & listItems.ItemData(varItem) & "& """ * """)"
            stStatePrint = stStatePrint & ", " & listItems.ItemData(varItem)
        Else
            stState = " And (([zz Profit and Loss Flow Final].[State]) Like """ * """ &" & listItems.ItemData(varItem) & "& """ * """)"
            stStatePrint = listItems.ItemData(varItem)
        End If
        MsgBox (stSate)
    Next varItem
    
    stSQL = "SELECT [zz Profit and Loss Flow Final].[Year-Month], Sum([zz Profit and Loss Flow Final].[Cases Shipped]) AS [Cases Shipped], Sum([zz Profit and Loss Flow Final].[Cases Depl]) AS [Cases Depl], Sum([zz Profit and Loss Flow Final].[Cases Depl Budget]) AS [Cases Depl Budget], Sum([zz Profit and Loss Flow Final].[Gross Profit]) AS [Gross Profit], Sum([zz Profit and Loss Flow Final].[SPAs]) AS SPAs, Sum([zz Profit and Loss Flow Final].[Samples]) AS Samples, Sum([zz Profit and Loss Flow Final].[Other Selling Exp]) AS OtherSellingExp, Sum([zz Profit and Loss Flow Final].[Salaries]) AS Salaries, Sum([zz Profit and Loss Flow Final].[Travel & Enter]) AS [Travel & Enter], Sum([zz Profit and Loss Flow Final].[Primary Budget]) AS [Primary Budget], Sum([zz Profit and Loss Flow Final].[T&E Bud]) AS [T&E Bud], Sum([zz Profit and Loss Flow Final].[Add'l Budget]) AS [Add'l Budget], Sum([zz Profit and Loss Flow Final].[Sal Budget]) AS [Sal Budget] INTO storeOutput" & vbCrLf & _
            "FROM [zz Profit and Loss Flow Final]" & vbCrLf & _
            "WHERE ((([zz Profit and Loss Flow Final].[Region]) Like " & Chr(34) & " * " & Chr(34) & " & [Forms]![Sort].[Territory] & " & Chr(34) & " * " & Chr(34) & ") And (([zz Profit and Loss Flow Final].[State]) Like " & Chr(34) & "*" & Chr(34) & " & [Forms]![Sort].[State] & " & Chr(34) & " * " & Chr(34) & ") And (([zz Profit and Loss Flow Final].[Brand]) Like " & Chr(34) & " * " & Chr(34) & " & [Forms]![Sort].[Brand] & " & Chr(34) & " * " & Chr(34) & "))" & vbCrLf & _
            "GROUP BY [zz Profit and Loss Flow Final].[Year-Month];"
            
            
    If Me.Territory <> "" Then
        stTerritory = Me.Territory
    Else
        stTerritory = "All"
    End If
    If stStatePrint = "" Then
        stState = "All"
    End If
    If Me.Brand <> "" Then
        stBrand = Me.Brand
    Else
        stBrand = "All"
    End If
    
    DoCmd.SetWarnings False
    'DoCmd.OpenQuery "makeOutput"
    DoCmd.SetWarnings True
    
    'create the recordset
    Set db = CurrentDb()
    db.Execute stSQL, dbFailOnError
    'Set qdf = db.CreateQueryDef("MakeOutput2", stSQL)
    
    
    'Query Parameters
    'qdf.Parameters("Region") = Me.Territory
    'qdf.Parameters("State") = Me.State
    'qdf.Parameters("Brand") = Me.Brand
    
    'Resolve the Parameters
    'For Each prm In qdf.Parameters
    '   prm.Value = Eval(prm.Name)
    'Next
    Set rs = db.OpenRecordset("storeOutput")
    
    'Set rs = qdf.OpenRecordset()
    'rs.MoveLast: rs.MoveFirst

    If rs.RecordCount = 0 Then Exit Sub

[CyberSoftHari]

You have to user ‘New’ Instances to create objects
[code=vb]
Dim db As DAO.Database
'To
Dim db As New DAO.Database
[/code]

[kirschey]

I now get, "Invalid use of New keyword"

[QVeen72]

Hi,

For DAO, not Necessary to Instantiate with "New" keyword..

Just try this, Wrap the Sates with Single Quotes, Instead of Double..:
And Use an "OR"

stState = stState & " ([zz Profit and Loss Flow Final].[State]) Like '*" & listItems.ItemD ata(varItem) & "*) ' OR "

finally strip off the last OR
If Trim(stState) <> "" Then
stState = Left(stState, Len(stState)-3)
End If

Also, In Your SQL Statement,

Remove this Part :
And (([zz Profit and Loss Flow Final].[State]) Like " & Chr(34) & "*" & Chr(34) & " & [Forms]![Sort].[State]

' No Need To Mention Again Field Name, It is already included in stState
Replace It With : And " & stState

Change the same for all the Fields


Regards
Veena