Re: How to open a report based on a multi-select listBox

From: Ken Snell [MVP] (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 01/31/05 

Date: Sun, 30 Jan 2005 21:54:51 -0500

I admit that I don't fully "see" your setup in my head, but what you would
"build" as the WHERE clause from your multiselect listbox might look
somethinng like this:

Dim strWhere As String
strWhere = "[Field1]=" & Me.lstGroupsSelect.Column (0) & _
    " And [Field2]=" & Me.lstGroupsSelect.Column(1)
DoCmd.OpenReport "ReportName", , , strWhere

If you have multiple values for a single field, then something like this as
you cycle through your listbox's selections:

Dim strWhere_Field1 As String, strWhere_Field2 As String
Dim strWhere As String
Dim lngLoop As Long
strWhere_Field1 = ""
strWhere_Field2 = ""
For lngLoop = 0 To Me.lstGroupsSelect.ListCount - 1
    If Me.lstGroupsSelect.Selected(lngLoop) = True Then
        strWhere_Field1 = "[Field1]=" & Me.lstGroupsSelect.Column (0) & _
            " And "
        strWhere_Field2 = "[Field2]=" & Me.lstGroupsSelect.Column (1) & _
            " And "
    End If
Next lngLoop
If strWhere_Field1 <> "" Then strWhere_Field1 = Left(strWhere_Field1, _
    Len(strWhere_Field1) - 5)
If strWhere_Field2 <> "" Then strWhere_Field2 = Left(strWhere_Field2, _
    Len(strWhere_Field1) - 5)
strWhere = strWhere_Field1 & " And " & strWhere_Field2
DoCmd.OpenReport "ReportName", , , strWhere 

-- 
        Ken Snell
<MS ACCESS MVP>
"Frederick Wilson" <faNOTTHISwilson@comcast.net> wrote in message 
news:Jf-dnYcPU5SVDWDcRVn-2A@comcast.com...
> So let me get this right
> with the where clause in the openreport I would do something like
> Field1=something1 or something2, field2=something1 or something2.
>
> I do not have any code because I do not know how to do this. I know what I 
> want to do but I can not figure it out.
>
> I have a report setup that gets everything from qrySignUpReport
>
> I have an unbound form frmSignUpReport with txtTitle, txtDate, ckActive, 
> lstGroupsSelect.
>
> The user types a name for the sign up in txtTitle
> The user types a date for the sign up in txtDate
> ckActive is a checkbox which will be used to match a checkbox in a 
> personnel list. If the person in tblPersonnel is active they have a 
> checkbox.
>
> Finally, the list box has two columns. I have my personnel broken down in 
> groups such as females, males and further defined as young, middle age, 
> seniors SOOOO
>
> you can have Female - young and/or female - middle age and so one.
>
> The idea of the list box is that you can select any of the combinations.
>
> I was thinking that the query would be something like 
> field1=lstGroupsSelect.column (0)
> field2=lstGroupsSelect.column (1)
>
> but when you can have a multi select I am not sure how to deal with it.
>
> Thanks,
> Fred
>
>
>
> Ken Snell [MVP] wrote:
>> If you're using ACCESS 2002 or 2003, yes, you can pass the SQL string in 
>> the OpenArgs argument of a DoCmd.OpenReport command. Although I'm not 
>> sure what you plan to do with the SQL statement then; I suppose you could 
>> use it in the report's Open event procedure to filter the report's 
>> recordsource.
>>
>> However, if you just want to filter the report when it's opened, use the 
>> fourth argument (the WhereCondition argument) of the DoCmd.OpenReport 
>> method to pass the filtering string. That filtering string would be a 
>> WHERE clause construction without the WHERE word.
>>

Relevant Pages

  • Re: OpenForm with Where Clause
    ... So how would I do a pattern match on a field that had data like DMH10257 or ... Dim strWhere As String ... of unbound search controls on different types of fields, ... about the Like condition in the where clause? ...
    (comp.databases.ms-access)
  • Re: Help! Outer Join problem
    ... dateTime type. ... you could end up with a problem of string comparisons. ... query and then bring that into another query with the other two tables. ... Try the following as the FROM clause (and you should be able to drop the ...
    (microsoft.public.access.queries)
  • Re: The mystery of the missing (compound) variables.
    ... its line (so I can recover it as a number) and a following space- ... separated text string line which can be PARSEd. ... to the second last END clause. ... other than READY  the THEN LEAVE clause passes control to the second ...
    (comp.lang.rexx)
  • Re: How to OpenRecordset(queryName refering to form)
    ... Yes, you can store the where clause in the table field, but when it's read in the code it will indeed be treated a string - stupid of me. ... It is still possible to do it in a similar fashion, though more complicated: you would need a separate table with a one to many relationship to the forms, one record per field to filter on with fields for FormName, FieldName, FormCOntrolName and type; then you would need to open this table as a recordset in your code, filtering on the form name, and loop through the records constructing the Where clause. ... Alternatively, you could use a convention like: no additional table, all the info for fields to filter on comes from the form itself: for all fields you wish to filter on, name the corresponding control on the form with a perfix followed by the field name, then loop in your code through the form controls and add to the where clause string for those controls starting fltr, extracting the field name from the control name -4)...). ...
    (microsoft.public.access.queries)
  • Re: Tracking Use of Reports
    ... Private Sub Openrpt_Click ... Dim strSql As String ... Ie Me.RptList.Value for ReportName & ...
    (microsoft.public.access.reports)