UtterAccess.com
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    
 
   
ohiogto1969
post May 24 2019, 05:11 PM
Post#1



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


Thanks,
John

Go to the top of the page
 
June7
post May 24 2019, 05:14 PM
Post#2



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
 
GroverParkGeorge
post May 24 2019, 06:05 PM
Post#3


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
 
ohiogto1969
post May 24 2019, 06:58 PM
Post#4



Posts: 144
Joined: 8-March 13



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



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
 
ADezii
post May 24 2019, 07:09 PM
Post#6



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:
    CODE
    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) & "','"
      Next
    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:
    SQL
    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