Multiple list boxes as report parameters (1 Viewer)

topdesk123

Registered User.
Local time
Today, 00:41
Joined
Mar 28, 2013
Messages
52
Good morning all!

Inherited another old database. There is a form (Projects by Salesperson Parameters) on which exists 6 list boxes (each with multi select set to extended), 2 start and end date groups, and 3 combo boxes. Right now I am just concerning myself with the list boxes (named: Salespeople, manufacturers (mfgs), productgroup, bidders and status.) The report to be opened is "ProjectsbySalesperson", its recordsource is "projectsbysalespersonquery" which contains all of the tables necessary to get the information requested.
I am trying to cobble together a combination of
Code:
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
AND
Code:
strWhere = strWhere & "([person] = """ & Me.Salespeople & """) AND "


I'd post the whole mess, but that's all it is, is a mess. Can't quite get my head around it. Since there are 6 different ctl.ItemData(varItem)s, how do I get them to produce the strwhere for my query? Or do I need to filter the report directly? So confused, so Monday. TIA!
 

Ranman256

Well-known member
Local time
Today, 03:41
Joined
Apr 9, 2015
Messages
4,339
using that many listboxes....
it will take a lot of code to create the where.

it may be faster/easier just to show the user everything, and let them ,right-click filter on fields they want to see.
 

topdesk123

Registered User.
Local time
Today, 00:41
Joined
Mar 28, 2013
Messages
52
Thanks RanMan, however, they cannot right click on the report. So far I have this (and I have changed the multi-select to none for now):
Code:
If Not IsNull(Me.Salespeople) Then
        strWhere = strWhere = strWhere & "'" & ctl.ItemData(varItem) & "'," And ""
    End If
    If Not IsNull(Me.MFGs) Then
        strWhere = strWhere & "([mfgs] Like ""*" & Me.MFGs & "*"") AND "
    End If    
    If Not IsNull(Me.ProductGroup) Then
        strWhere = strWhere & "([ProductGroup] Like ""*" & Me.ProductGroup & "*"") AND "
    End If    
    If Not IsNull(Me.Projects) Then
        strWhere = strWhere & "([ProjectName] Like ""*" & Me.Projects & "*"") AND "
    End If
    If Not IsNull(Me.Bidders) Then
        strWhere = strWhere & "([bidder] Like ""*" & Me.Bidders & "*"") AND "
    End If
        If Not IsNull(Me.Status) Then
        strWhere = strWhere & "([status] Like ""*" & Me.Status & "*"") AND "
    If Not IsNull(Me.MinAmt) Then
        strWhere = strWhere & "([itembid] = " & Me.MinAmt & ") AND "
    End If
    If Not IsNull(Me.BidStartDate) Then
        strWhere = strWhere & "([biddate] >= " & Format(Me.BidStartDate, conJetDate) & ") AND "
    End If
    If Not IsNull(Me.BidStartDate) Then   
        strWhere = strWhere & "([biddate] < " & Format(Me.BidEndDate, conJetDate) & ") AND "
    End If

    lngLen = Len(strWhere) - 5
    If lngLen <= 0 then
        MsgBox "No criteria", vbInformation, 
    else
        strWhere = Left$(strWhere, lngLen)
        'Debug.Print strWhere
    End If
End Sub

Thanks again for the look!
 

topdesk123

Registered User.
Local time
Today, 00:41
Joined
Mar 28, 2013
Messages
52
I just found out that the list boxes NEED to be multi-select. :-(
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:41
Joined
Oct 29, 2018
Messages
21,358
Hi,


Not sure exactly what your requirements are but I imagine in the end you'll have a criteria as follows:


WHERE (FieldName1 In('stuff','from','multi','listbox1')) AND (FieldName2 In('listbox2','items','here')) AND (FieldName3=MaybeTextboxValueHere)) etc.


Is this correct?
 

Cronk

Registered User.
Local time
Today, 18:41
Joined
Jul 4, 2013
Messages
2,770
I too will use the construct

where <field> is in (value1, value2,...)


strWhere = strWhere & "([person] = """ & Me.Salespeople & """) AND "
Be careful with the AND in situations like this. Unless your salesmen are working in teams, there won't be any sales by salesman 1 AND salesman 2.


My go to question of confused users is "how many people live in London and New York". Answer is none, because someone only lives in one OR the other place.
 

topdesk123

Registered User.
Local time
Today, 00:41
Joined
Mar 28, 2013
Messages
52
Yep, that looks about right. Good point about the salesmen, I was contemplating that as well, so in the first criteria what would my syntax be? Since I need both salesmen on the report, with each of their related information...ugh...mental gymnastics at work. THANKS!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:41
Joined
Oct 29, 2018
Messages
21,358
Hi,


Are you able to post a sample copy of your db, so we can help you build this a little bit at a time?


Just a thought... (things might go faster if we had something to work with)
 

topdesk123

Registered User.
Local time
Today, 00:41
Joined
Mar 28, 2013
Messages
52
I will try to get one posted tomorrow. I started to clean it out and screwed it up. Hubby's hungry...must feed him. :) TTYT.
 

moke123

AWF VIP
Local time
Today, 03:41
Joined
Jan 11, 2013
Messages
3,852
I use a public function when using multi select list boxes. this iterates through the selected items in the listbox and returns an array.

Code:
Public Function getLBX(lbx As ListBox, Optional intColumn As Variant = 0, Optional Seperator As String = ",", _
                       Optional Delim As Variant = Null) As String

'Iterates thru the multiselect listbox and constructs an array of the selected items
'Arguments:
'Lbx is Listbox Object ie.Me.MyListbox
'intColumn is the column # to be returned
'Seperator is the character seperating items in array returned
'Delim is optional delimiter to be return in array ie. #1/1/2001#,#12/25/2015#

    Dim strList As String
    Dim varSelected As Variant

    'On Error GoTo getLBX_Error

    If lbx.ItemsSelected.Count = 0 Then
        'MsgBox "Nothing selected"
    Else

        For Each varSelected In lbx.ItemsSelected

            If Nz(lbx.Column(intColumn, (varSelected)), "") <> "" Then

                strList = strList & Delim & lbx.Column(intColumn, (varSelected)) & Delim & Seperator

            Else

                strList = strList

            End If

        Next varSelected

        If Nz(strList, "") <> "" Then

            strList = Left$(strList, Len(strList) - 1)  'remove trailing comma

        End If
    End If

    getLBX = strList

    On Error GoTo 0
    Exit Function

getLBX_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getLBX of Module modLBX"

End Function

to use this with an In clause you would do something like

Code:
...where  somefield in (" & getLBX(me.mylistboxname) & ")"
 

topdesk123

Registered User.
Local time
Today, 00:41
Joined
Mar 28, 2013
Messages
52
Good morning!

Here is a link to the original db I inherited (too big to post no matter what I do).https://www.dropbox.com/s/7gmo620ep909kyz/PT_FE_New_Fast_Testb - Copy.zip?dl=0

The section I am trying to re-write is the reports, right now, it takes the users about an hour to run the sales report. I have some instructions within the db on where to go, etc. I am stripping all of the code out from behind the param form. And want to do what I have posted to see if it will run faster. I've tested the recordsource query with my own criteria and it HAS run much faster. Thank you so much for your brains! :) ~PEACE~
 

topdesk123

Registered User.
Local time
Today, 00:41
Joined
Mar 28, 2013
Messages
52
In regards to the above post, looking for opinions, do you think it's quicker to perform the filter in an "On format" event of the report, or on a command button that opens the report with criteria? Thanks!
 

Users who are viewing this thread

Top Bottom