Filter reports based on 2 multiselect listboxes

[Chantal]

Hello, I need help with the following code which is working perfectly. However, I'm unable to add the Where clause which will enable the users to filter reports by year and/or by month.
These 2 criterias are listed in 2 unbound multiselect value listboxes (lstYear and lstMonth) and should be optional.
Thank you!
Chantal

Private Sub cmdPreview_Click()
On Error GoTo cmdPreview_ClickErr

If Not IsNull(Me.lstReports) Then
DoCmd.OpenReport Me.lstReports, acViewPreview
End If
Exit Sub
cmdPreview_ClickErr:
'Select Case Err.Number
' MsgBox "Report cancelled, or no matching data.", vbInformation, "Information"
'Case Else
' MsgBox "Error " & Err & ": " & Error$, vbInformation, "cmdPreview_Click()"
'End Select
Resume Next
End Sub

Private Sub cmdPrint_Click()
On Error GoTo cmdPrint_ClickErr
If Not IsNull(Me.lstReports) Then
DoCmd.OpenReport Me.lstReports, acViewNormal
End If
Exit Sub
cmdPrint_ClickErr:
'Select Case Err.Number
' MsgBox "Report cancelled, or no matching data.", vbInformation, "Information"
'Case Else
' MsgBox "Error " & Err & ": " & Error$, vbInformation, "cmdPrint_Click()"
'End Select
Resume Next

End Sub

 

[JamesMcS]

I'm guessing the reports are based on a query? You'll need to put an absolute reference to the combo boxes in as criteria in the relevant fields in the query:

forms![formname].form!comboname

I think... this will pull back the bound column value of the combo, not sure about it being optional though - if there's nothing in the combo, nothing will be pulled to the query, so it should be OK.

 

[Chantal]

Thanks James...
Yeah, it's based on a query. I tried the references before and it resulted in a no data error. I even tried to add the Like "*" at the beginning of the references which resulted in including all the data in the reports, not just only the ones I selected.
It's ok if it ended up being obligatory, I'll probably add the All option in the listboxes.
:-(

 

[JamesMcS]

So the query just came up blank? Make sure the column in the query relates to the boun column of the combo box - or put forms![formname].form!comboname.column(x) instead, where x is the column number you're looking for criteria in, starting from 0.

Try just doing one for now and see if it works.

 

[Chantal]

Yup the query came up blank... The Lists are actually value listboxes and not comboboxes, I'm not sure if that's the problem.
I guess we need to write the codes instead of refrencing the query, just as I did with the first listbox through which users can choose the report they need. Unfortunately, I don't know how to write the code with more than one variable.

Thanks,
Chantal

 

[JamesMcS]

Ah sorry I misread. It should still be OK though - try creating a text box on your form whose controlsource is the listbox, and see what appears in it when you change values maybe?

 

[Chantal]

James, I forgot to mention that the query works fine when i reference it as following:
Like "*" & [Forms]![Reports Dialog]![lstYear]
Like "*" & [Forms]![Reports Dialog]![lstMonth]

But i don't get the needed information when I choose to generate the report from the Report Dialog form. So I guess the form is the problem, right :-s ?!

Thanks

 

[Chantal]

ok I'll try

 

[JamesMcS]

If the criteria works Ok like that, that would suggest that the values in the list box aren't the same as the values in the query result. Does the report work by istelf?

 

[Chantal]

Yup... it just asks me to enter the parameter values

 

[JamesMcS]

lstyear and lstmonth right? And what are the text boxes bound to the list boxes saying?

 

[Chantal]

Yup that's right...
The months from January to December, and the years from 2010 to 2012

 

[JamesMcS]

And they match the data in the query fields you're putting the criteria in? I'm lost now... can you upload a stripped copy so I can have a look?

 

[KiEESH]

Hi!

Working on a similar project where users can make selections from one of two listboxes to run a query.

My code worked fine for just one listbox ('Phases').

Phase is numerical and Zone is text.

Having trouble incorporating the right code to have Access check one list OR the other ('Zones'). :banghead:

The trouble started when I added 'Zones' into the mix.

My latest attempt returns an error message "Data Type Mismatch."


Any advice is appreciated.

Option Compare Database
Private Sub btn_RunAdHocQry_Click()
    On Error GoTo Err_btn_RunAdHocQry_Click
    Dim MyDB As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim i As Integer
    Dim strSQL As String
    Dim strWhere As String
    Dim strIN As String
    Dim flgSelectAll As Boolean
    Dim varItem As Variant
    '-----------------------------------------------------------------------------
       Set MyDB = CurrentDb()
    '------------------------------------------------------------------------------
    strSQL = "SELECT * FROM AREA_GROWTH2"
    '------------------------------------------------------------------------------
    'Build the IN string by looping through the listbox
    For i = 0 To lst_AdHocPhase.ListCount - 1 Or lst_Zone.ListCount - 1
        If lst_AdHocPhase.Selected(i) Or lst_Zone.Selected(i) Then
            If lst_AdHocPhase.Column(0, i) = "All" Or lst_Zone.Column(0, i) = "All" Then
                flgSelectAll = True
            End If
            strIN = strIN & "'" & lst_AdHocPhase.Column(0, i) & "'," Or strIN = strIN & "'" & lst_Zone.Column(0, i) & "',"
        End If
    Next i
 
        '------------------------------------------------------------------------------
    'Create the WHERE string, and strip off the last comma of the IN string
    strWhere = " WHERE [Phase] Or [Zone] in " & _
               "(" & Left(strIN, Len(strIN) - 1) & ")"
    '------------------------------------------------------------------------------
    'If "All" was selected in the listbox, don't add the WHERE condition
    If Not flgSelectAll Then
        strSQL = strSQL & strWhere
    End If
    '------------------------------------------------------------------------------
    MyDB.QueryDefs.Delete "qry_AdHoc"
    Set qdef = MyDB.CreateQueryDef("qry_AdHoc", strSQL)
    '------------------------------------------------------------------------------
    'Open the query, built using the IN clause to set the criteria
    DoCmd.OpenQuery "qry_AdHoc", acViewNormal
    DoCmd.Close
    strfilename = "C:\Documents and Settings\" & Environ("username") & "\Desktop\AGDB_AdHocQuery.xls "
    DoCmd.TransferSpreadsheet acExport, , "qry_AdHoc", strfilename
    MsgBox "Successful Export to Desktop", , "File Export"
 
    '------------------------------------------------------------------------------
 
    'Clear listbox selection after running query
    For Each varItem In Me.lst_AdHocPhase.ItemsSelected
        Me.lst_AdHocPhase.Selected(varItem) = False
    Next varItem
 
    '-------------------------------------------------------------------------------
 
 
Exit_btn_RunAdHocQry_Click:
    Exit Sub
Err_btn_RunAdHocQry_Click:
    If Err.Number = 5 Then
        MsgBox "You must make a selection from the list" _
               , , "Selection Required !"
        Resume Exit_btn_RunAdHocQry_Click
    Else
        'Write out the error and exit the sub
        MsgBox Err.Description
        Resume Exit_btn_RunAdHocQry_Click
    End If
 
End Sub