Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

Multiselect for Report Criteria

I have a form with a multiselect list box and another box that multiselect choices are copied to.  There is also an option group with two members.  I am trying to assign code to the Click event of a command button so that when someone makes their choices from the multiselect, then specifies the report they want to view, the proper report will come up showing the information filtered by the multiselect choices.

I'm having a little trouble with this.  Any guidance?
0
strovej
Asked:
strovej
1 Solution
 
tomookCommented:
DoCmd.OpenReport has an optional fourth argument which allows you to specify a WHERE clause, without the WHERE. All you have to do is build the criteria based on the selected items in your list box, and pass it to the DoCmd.OpenReport.
ex. (not tested)
Dim sWhere As String
Dim iCnt1 As Integer

sWhere = ""
For iCnt1 = 1 To Me.SomeListBox.ItemsSelected.Count
    sWhere = sWhere & " AND (FieldInReportQuery = " Me.SomeListBox.ItemsSelected(iCnt1) & ")"
    ' Will need to paste in quotes if your values are strings,
    ' # if dates, ex.
    'sWhere = sWhere & " AND (FieldInReportQuery = '" Me.SomeListBox.ItemsSelected(iCnt1) & "')"

    'sWhere = sWhere & " AND (FieldInReportQuery = #" Me.SomeListBox.ItemsSelected(iCnt1) & "#)"

Next iCnt1
' Strip of first " AND "
If Len(sWhere) > 0 Then
    sWhere = Mid$(sWhere,5)
End If
DoCmd.OpenReport "ReportName", acNormal, , sWhere

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now