Multiple multi select list boxes to open report filtered to chosen data

[Gina Maylone]

Dear People who know so much more than I,



I'm trying to pass the value of 5 multiselect list boxes, a couple of date fields, a currency field, maybe or maybe not all of them. You know users. I know my strwhere with the dates is out of place.

Private Sub OpenReportButton_Click()'DoCmd.SetWarnings False


Dim ctlList As Variant
Dim ctlList2 As Variant
Dim ctlList3 As Variant
Dim ctlList4 As Variant
Dim ctlList5 As Variant
Dim Lmnt As Variant
Dim Lmnt2 As Variant
Dim Lmnt3 As Variant
Dim Lmnt4 As Variant
Dim Lmnt5 As Variant


    Dim strDelim As String      'Delimiter for this field type.


Dim sSql As String
Dim sSql2 As String
Dim sSql3 As String
Dim sSql4 As String
Dim sSql5 As String


Dim mycriteria As String




Set ctlList = [Forms]![FReportParams]!Salespeople
Set ctlList2 = [Forms]![FReportParams]!Bidders
Set ctlList3 = [Forms]![FReportParams]!ProdGrp
Set ctlList4 = [Forms]![FReportParams]!Projects
Set ctlList5 = [Forms]![FReportParams]!status


strDelim = """"


   'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.


'    'Date field example. Use the format string to add the # delimiters and get the right international format.
    If Not IsNull([Forms]![FReportParams]![BidStartDate_BeginR]) Then
        strWhere = strWhere & "([biddate] >= " & Format([Forms]![FReportParams]![BidStartDate_BeginR], conJetDate) & ") AND "
 '       Debug.Print bidate & " " & BidStartDate_BeginR
    End If
    If Not IsNull([Forms]![FReportParams]![BidstartDate_EndR]) Then
        strWhere = strWhere & "([biddate] <= " & Format([Forms]![FReportParams]![BidstartDate_EndR]) & ") AND "
 '               Debug.Print bidate & " " & BidstartDate_EndR


    End If


sSql = "SELECT * FROM masterquery WHERE topersonid IN ("
For Each Lmnt In ctlList.itemsselected
    sSql = sSql & ctlList.ItemData(Lmnt) & ","
Next
'Remove Last comma and single quote and add closing bracket
sSql = Left(sSql, Len(sSql) - 1) & ")"
Debug.Print sSql


sSql3 = "SELECT * FROM masterquery WHERE ProductGrpID IN ("
For Each Lmnt3 In ctlList3.itemsselected
    sSql3 = sSql3 & ctlList3.ItemData(Lmnt) & ","
Next
sSql3 = Left(sSql3, Len(sSql3) - 1) & ")" 'Remove Last comma and single quote and add closing bracket
Debug.Print "SSQL3 " & sSql3


sSql4 = "SELECT * FROM masterquery WHERE projectno IN ("
For Each Lmnt In ctlList4.itemsselected
    sSql4 = sSql4 & strDelim & ctlList4.ItemData(Lmnt) & strDelim & ","
Next
sSql4 = Left(sSql4, Len(sSql4) - 1) & ")" 'Remove Last comma and single quote and add closing bracket
Debug.Print "SSQL4 " & sSql4


sSql5 = "SELECT * FROM masterquery WHERE bidstatus IN ("
For Each Lmnt In ctlList5.itemsselected
    sSql5 = sSql5 & ctlList5.ItemData(Lmnt) & ","
Next
sSql5 = Left(sSql5, Len(sSql5) - 1) & ")" 'Remove Last comma and single quote and add closing bracket
Debug.Print "SSQL5 " & sSql5


sSql2 = "SELECT * FROM masterquery WHERE Bidders IN ("
For Each Lmnt In ctlList2.itemsselected
    sSql2 = sSql2 & ctlList2.ItemData(Lmnt) & ","
Next
sSql2 = Left(sSql2, Len(sSql2) - 1) & ")" 'Remove Last comma and single quote and add closing bracket
Debug.Print "SSQL2 " & sSql2




'DoCmd.Openreport Forms![fMainMenu]![FReports].Form![ReportName].Column(3), acViewPreview, mycriteria




DoCmd.SetWarnings False




Forms("freportparams").SetFocus
    DoCmd.Minimize
    DoCmd.SetWarnings True
    
End Sub

The immediate window shows me the correct syntax for each variable(?).

SSQL   SELECT * FROM masterquery WHERE topersonid IN (8,28)
SSQL3 SELECT * FROM masterquery WHERE ProductGrpID IN (197)
SSQL4 SELECT * FROM masterquery WHERE projectno IN ("09-29-20-07","09-30-20-010")
SSQL5 SELECT * FROM masterquery WHERE bidstatus IN (1)
SSQL2 SELECT * FROM masterquery WHERE Bidders IN (7,8)

How do I open the report and pass this information into it? As always, THANK YOU in advance!!!!!

[ranman256]

If you use a listbox to dbl-click the item wanted,
it then runs an append query to add the item to a tPickedList table.

Then your report query joins this tPickedList to the main data to get only those items.
ZERO programming involved, just 1 append query.

[Gina Maylone]

Thanks Ranman! I will try this! Will they be able to choose All? I have a button above each listbox that allows them to, and one to clear all.

[kd2017]

... I know my strwhere with the dates is out of place...

The immediate window shows me the correct syntax for each variable(?).

SELECT * FROM masterquery WHERE topersonid IN (8,28)SSQL3 
SELECT * FROM masterquery WHERE ProductGrpID IN (197)
SSQL4 SELECT * FROM masterquery WHERE projectno IN ("09-29-20-07","09-30-20-010")
SSQL5 SELECT * FROM masterquery WHERE bidstatus IN (1)
SSQL2 SELECT * FROM masterquery WHERE Bidders IN (7,8)

you want to surround dates with # instead of " so

SELECT * FROM masterquery WHERE projectno IN (#09-29-20-07#,#09-30-20-010#)

How do I open the report and pass this information into it? As always, THANK YOU in advance!!!!!

[/QUOTE]
Can we get a look at this report? How does it tie all these queries together? Is it a bunch of subreports? There are probably a few ways to skin this cat.

[Micron]

#09-29-20-07#

That is a date?? It's in the Projectno field? The output doesn't seem to contain any field that appears to be a date to me.

[Gina Maylone]

Hi kd2017, thanks for your time! These actually aren't dates, ("09-29-20-07","09-30-20-010"). They are project numbers that the users assign. The date fields are closer to the top:

  If Not IsNull([Forms]![FReportParams]![BidStartDate_BeginR]) Then        strWhere = strWhere & "([biddate] >= " & Format([Forms]![FReportParams]![BidStartDate_BeginR], conJetDate) & ") AND "
 '       Debug.Print bidate & " " & BidStartDate_BeginR
    End If
    If Not IsNull([Forms]![FReportParams]![BidstartDate_EndR]) Then
        strWhere = strWhere & "([biddate] <= " & Format([Forms]![FReportParams]![BidstartDate_EndR]) & ") AND "
 '               Debug.Print bidate & " " & BidstartDate_EndR


    End If

I will clean up my db and attach for your reference. Thanks again!

[kd2017]

Oops, my mistake!

[Gina Maylone]

Here it is.
sampledb.zip

Login and PW are on main menu. Once you log in, click on the reports tab.
Double click on Management Project Report, the param form will open.
You can choose all from each listbox, then click the report button, it won't actually open the report right now, you can ctrl-g to check the immediate window.

The name of the report for this particular function is RFollowUp, subreports are Rfollowupbidders and Rfollowupproducts, though I need the function to work on all of the reports on the reports menu. NOT the comparison report, that's working fine a different way.

Please ignore the text boxes under the listboxes, I was trying different things, they don't play a part in this right now.

Thank you so very much!!!

[kd2017]

Ok, so I'm thinking you don't need to build the 5 different queries. All you need to do is concat those where clauses and pass it to the docmd.openreport method: https://docs.microsoft.com/en-us/off...cmd.openreport

Dim my_criteria AS String

if Not IsNull( Me.Salespeople ) Then
'my_criteria = build an In clause of selected sales people
end if

If Not IsNull( Me.MFGs ) Then
'my_criteria = my_criteria & build an In clause for selected manufacturers
End If

'... so on and so forth

DoCmd.OpenReport "RFollowUp", , , my_criteria

NOTE: I'm not sure off the top of my head if IsNull is appropriate for multiselect listboxes...

Essentially you want to make a search form like this example: http://allenbrowne.com/ser-62.html

[Gina Maylone]

Thank you kd2017, I will try this. I started out with one of Allen's codes - he's a genius! But then I got to needing to concatenate the where clauses and my head exploded . I'll let you know how it goes. Thanks again.

[ssanfu]

Hi Gina,

On the form "FReportParams", there is a button that opens the report "RFollowup".
The Event Procedure is OpenReportButton_Click()...... I rewrote the code and the report opens (for me).
I also moved some of the code......

Private Sub OpenReportButton_Click()
    Dim ctlList1 As Variant
    Dim ctlList2 As Variant
    Dim ctlList3 As Variant
    Dim ctlList4 As Variant
    Dim ctlList5 As Variant
    Dim Lmnt As Variant
    Dim strDelim As String      'Delimiter for this field type.
    Dim sSql As String

    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.

    strDelim = """"

    Set ctlList1 = Me.Salespeople
    Set ctlList2 = Me.Bidders
    Set ctlList3 = Me.ProdGrp
    Set ctlList4 = Me.Projects
    Set ctlList5 = Me.status

   'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
                        

'    'Date field example. Use the format string to add the # delimiters and get the right international format.
'    If Not IsNull([Forms]![FReportParams]![BidStartDate_BeginR]) Then
    If Not IsNull(Me.BidStartDate_BeginR) Then
        strWhere = strWhere & " [biddate] >= " & Format(Me.BidStartDate_BeginR, conJetDate) & " AND "
    End If
    If Not IsNull(Me.BidstartDate_EndR) Then
        strWhere = strWhere & "[biddate] <= " & Format(Me.BidstartDate_EndR, conJetDate) & " AND "
    End If

    '------ Salespeople listbox
'    Debug.Print ctlList1.itemsselected.Count & "   Salespeople selected"
    If ctlList1.itemsselected.Count > 0 Then '<< check for items selected
        sSql = "topersonid IN ("
        For Each Lmnt In ctlList1.itemsselected
            sSql = sSql & ctlList1.ItemData(Lmnt) & ","
        Next
        sSql = Left(sSql, Len(sSql) - 1) & ")"
        strWhere = strWhere & sSql & " AND "
    End If
 '    Debug.Print strWhere   
 
    '------ProdGrp listbox
'    Debug.Print ctlList3.itemsselected.Count & "   ProdGrp selected"
    If ctlList3.itemsselected.Count > 0 Then '<< check for items selected
        sSql = "ProductGrpID IN ("
        For Each Lmnt In ctlList3.itemsselected
            sSql = sSql & ctlList3.ItemData(Lmnt) & ","
        Next
        sSql = Left(sSql, Len(sSql) - 1) & ")" 'Remove Last comma and single quote and add closing bracket
        strWhere = strWhere & sSql & " AND "
    End If
    '    Debug.Print strWhere
    
    '----------Projects LISTbox
'    Debug.Print ctlList4.itemsselected.Count & "   Projects selected"
    If ctlList4.itemsselected.Count > 0 Then '<< check for items selected
        sSql = "projectno IN ("
        For Each Lmnt In ctlList4.itemsselected
            sSql = sSql & strDelim & ctlList4.ItemData(Lmnt) & strDelim & ","
        Next
        sSql = Left(sSql, Len(sSql) - 1) & ")" 'Remove Last comma and single quote and add closing bracket
        strWhere = strWhere & sSql & " AND "
    End If
    '    Debug.Print strWhere
        
    '----------Status listbox
'    Debug.Print ctlList5.itemsselected.Count & "   Statuses selected"
    If ctlList5.itemsselected.Count > 0 Then '<< check for items selected
        sSql = "bidstatus IN ("
        For Each Lmnt In ctlList5.itemsselected
            sSql = sSql & ctlList5.ItemData(Lmnt) & ","
        Next
        sSql = Left(sSql, Len(sSql) - 1) & ")" 'Remove Last comma and single quote and add closing bracket
        strWhere = strWhere & sSql & " AND "
    End If
    '    Debug.Print strWhere
        
    '----------Bidders listbox
'    Debug.Print ctlList2.itemsselected.Count & "   Bidders selected"
    If ctlList2.itemsselected.Count > 0 Then '<< check for items selected
        sSql = "BidStatusID IN ("
        For Each Lmnt In ctlList2.itemsselected
            sSql = sSql & ctlList2.ItemData(Lmnt) & ","
        Next
        sSql = Left(sSql, Len(sSql) - 1) & ")" 'Remove Last comma and single quote and add closing bracket
        strWhere = strWhere & sSql & " AND "
    End If
'    Debug.Print strWhere
    
    
    'check length of strWhere
    If Len(strWhere) > 0 Then
        'remove the trailing " AND " 
        strWhere = Left(strWhere, Len(strWhere) - 5)
    End If
    
    '------- open report in Print Preview mode -------------

    DoCmd.Openreport "RFollowup", acViewPreview, , strWhere

    '---------------------------------------------------------


    '????? don't know what these lines are for...
    Forms("freportparams").SetFocus
    DoCmd.Minimize '
    
End Sub

[Micron]

What I find odd about that code is declaring a variant variable then assigning an object to it. When I use a variant, it is because it has the potential to contain at least two data types (e.g. text or number; null or anything else and so on). I'd never assign an object to a variant if an object is all it will ever be, which seems to be the case here. Plus any time I SET something I typically SET it to Nothing before procedure exit/end. That isn't being done here, and really isn't necessary if the variable is assigned what the control contains instead. Setting it to an object then writing that it equals its value doesn't cover the possibility that its value may be Null.

Or have I had too many wobbly pops and inhaled too much walnut wood dust, because my superiors haven't called this out?

[ssanfu]

@Micron,
Good point - I would say you are spot on. I was focused on just getting the code to open the report.
I looked at the "SET" commands and then just skipped over them. So, testing tomorrow (as well as more snow).
Its 7pm - time to go home and eat. Maybe I will be able to espy a herd of wobbly pops - but its pretty dark right now and deep snow........

[moke123]

inhaled too much walnut wood dust

Glad to see your still working on my guitar

I stripped out all the spaghetti and added some simple code to construct a where clause. Its not how I think I would do it but then again I'm lost trying to figure out what you have.
hopefully it will help demonstrate basic principle.

you would then use the where clause in your docmd.openreport procedure.

[Gina Maylone]

Hey Steve! How you doin' up there in the frozen tundra? We've had a pretty easy winter so far, except for the 9" of snow in October. Ish.

As always, your brilliance has blown me away! I had to adjust a couple of things, but now it looks like I'm getting correct data. Going to test some more.

I hope you've been well my friend, it's been a long time! I'm getting away from the business, starting a real job on Tuesday. Not sure if it will make me or break me.

Thank you so much for your time and effort!!