Solved

Multiselect for Report Criteria

Posted on 1998-08-14
1
267 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 50 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 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