?
Solved

Multiselect for Report Criteria

Posted on 1998-08-14
1
Medium Priority
?
274 Views
Last Modified: 2012-05-04
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
Comment
Question by:strovej
1 Comment
 
LVL 4

Accepted Solution

by:
tomook earned 150 total points
ID: 1959226
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

585 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question