X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Multiple Multi Select List Box To Filter A Report, Access 2016    
post May 24 2019, 05:11 PM

Posts: 144
Joined: 8-March 13

Hello All,

I have a form that has two multi-select list boxes followed by two unbound text boxes. The first list box has a list of job types eg; "Roofing", "Siding", "Windows", the second list box is a list of salesmen, eg; "Brian", "Dave", "Joe", lastly the two text boxes are for dates, the first one is for a start date, the second one is for an end date.

Scenario: I would like to filter my report by two job types from the first list box, two salesman from the second list box and a start date of 01/01/2019 and an end date of 05/23/2019.

This seems kind of complex in my trials. Is there a better way of going about this or is my idea viable?

Any examples or pointers would be appreciated.

I am attaching an image of my form and sample report which may give you a better idea of what I am trying to do. Attached File  utter.zip ( 34.86K )Number of downloads: 4


Go to the top of the page
post May 24 2019, 05:14 PM

Posts: 599
Joined: 25-January 16

Requires VBA code looping through listbox selection to build criteria, usually to build an array set for the IN() function. Review http://allenbrowne.com/ser-50.html

Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
post May 24 2019, 06:05 PM

UA Admin
Posts: 35,130
Joined: 20-June 02
From: Newcastle, WA

I have a demo database on my website that does something similar. See if it can give you some ideas. Allen Browne is always the best, though...

My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post May 24 2019, 06:58 PM

Posts: 144
Joined: 8-March 13

Thanks thanks.gif
Go to the top of the page
post May 24 2019, 07:00 PM

Posts: 144
Joined: 8-March 13

I am going to look at both, as always I appreciate the help.
Go to the top of the page
post May 24 2019, 07:09 PM

Posts: 2,372
Joined: 4-February 07
From: USA, Florida, Delray Beach

  1. You can dynamically Build the Record Source for your Report. I created a Demo for you that utilizes a single Multi-Select List Box for workers (lstWorkers) as well as your specified Date Range.
  2. Code Definition:
    Dim varItm As Variant
    Dim lst As Access.ListBox
    Dim strBuild As String

    Set lst = Me![lstWorkers]

    With lst
      If .ItemsSelected.Count < 2 Then Exit Sub       'need at least 2
      For Each varItm In .ItemsSelected
        strBuild = strBuild & lst.ItemData(varItm) & "','"
    End With

    Debug.Print "SELECT * FROM tblWorkers WHERE [Worker] IN('" & Left$(strBuild, Len(strBuild) - 2) & _
                ") AND [Work Date] BETWEEN #5/1/2019# AND #5/22/2019#"
  3. Sample OUTPUT after multiple Workers selected:
    SELECT * FROM tblWorkers WHERE [Worker] IN('Bianco, Joe','Groover, Brian','Hohvart, John','Jamieson, Dewey') AND [Work Date] BETWEEN #5/1/2019# AND #5/22/2019#
  4. Not to deviate, but the Last and First Names should never be stored in a single Field.
  5. This base Code can easily be expanded to include Job Descriptions.
  6. Hope this helps.

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    18th June 2019 - 06:19 AM