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
> Filter Multiple Criterias From List Box, Access 2016    
 
   
vmaulit
post Aug 23 2019, 01:16 PM
Post#1



Posts: 1
Joined: 8-August 19



Hi All,

I currently have a database in which I have 3 combo boxes that create a filter string. My goal now is to change these 3 boxes to list boxes so I can choose more than one criteria to filter from each field. However, I am having trouble creating the code.

You can see below that I have 3 fields for Filters. me.Submitter_Filt and me.Status_Filt are currently combo boxes, I am currently on changing Product Filt from Combo to List box. However I get a runtime 3075 error when the code tries to turn filter on (refer to code). Any better way to do this? thanks in advance!

me.Submitter_Filt
me.Status_Filt
me.Product_Filt

CODE
Sub BuildFiltStr()
Dim Product_Filt_Variant As Variant

    FiltStr = ""
    If Me.Submitter_Filt <> "" Then
       FiltStr = "[Submitter] = '" & Me.Submitter_Filt & "'"
    End If
    
    If Me.Status_Filt <> "" Then
       If FiltStr = "" Then
          FiltStr = "[Proposal Status] ='" & Me.Status_Filt & "'"
       Else
          FiltStr = FiltStr & " AND [Proposal Status] = '" & Me.Status_Filt & "'"
       End If
    End If



'   New Code for LISTBOX Filter

    If Me.Product_Filt.ItemsSelected.Count = 0 Then
       FiltStr = FiltStr
    ElseIf Me.Product_Filt.ItemsSelected.Count = 1 Then
       If FiltStr = "" Then
          FiltStr = "[Product Type] = '" & Me.Product_Filt.ItemData(Product_Filt_Variant)
       Else
          FiltStr = FiltStr & "AND [Product Type] = '" & Me.Product_Filt.ItemData(Product_Filt_Variant)
       End If
    Else
       If FiltStr = "" Then
          For Each Product_Filt_Variant In Me.Product_Filt.ItemsSelected
             FiltStr = FiltStr & "[Product Type] = '" & Me.Product_Filt.ItemData(Product_Filt_Variant) & "'"
          Next
       Else
          For Each Product_Filt_Variant In Me.Product_Filt.ItemsSelected
             FiltStr = FiltStr & "AND [Product Type] ='" & Me.Product_Filt.ItemData(Product_Filt_Variant) & "'"
          Next
       End If
   End If
  
          
'    Old Code for Product_Filt COMBOBOX Filter
      
'    If Me.Product_Filt <> "" Then
'       If FiltStr = "" Then
'          FiltStr = "[Product Type] = '" & Me.Product_Filt & "'"
'       Else
'          FiltStr = FiltStr & " AND [Product Type] = '" & Me.Product_Filt & "'"
'       End If
'    End If
      
    If FiltStr = "" Then
       Me.Filter = ""
       Me.FilterOn = False
    Else
       Me.Filter = FiltStr
       Me.FilterOn = True    'I GET A RUNTIME ERROR 3075 ERROR HERE DUE TO SYNTAX OF LISTBOX FILTER
    End If
      
    
    Me.Funnel_Total_Cost = "" 'clearing value before next step
    Me.Funnel_Total_Cost = DSum("[Equipment and Tooling Expense]", "Funnel", FiltStr) + DSum("[Misc Cost]", "Funnel", FiltStr) + DSum("[Testing Cost]", "Funnel", FiltStr) + (DSum("[Man Power (hrs)]", "Funnel", FiltStr) * 60)

    Me.Funnel_Total_Savings = "" 'clearing value before next step
    Me.Funnel_Total_Savings = DSum("[Material Cost Savings (Annual)]", "Funnel", FiltStr) + DSum("[Labor Cost Savings (Annual)]", "Funnel", FiltStr) + DSum("[Overhead/Burden Cost Savings (Annual)]", "Funnel", FiltStr) + DSum("[Warranty Savings (Annual)]", "Funnel", FiltStr) + DSum("[Transport & Logistics Cost Savings (Annual)]", "Funnel", FiltStr) + DSum("[Other Savings (Annual)]", "Funnel", FiltStr)
    
    
    SubmitterStr = Nz(Me.Submitter_Filt, "")
    ProductStr = Nz(Me.Product_Filt, "")
    StatusStr = Nz(Me.Status_Filt, "")
    
End Sub
Go to the top of the page
 
GroverParkGeorge
post Aug 23 2019, 01:22 PM
Post#2


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


Welcome to UtterAccess.

Check out the demo database on my website. It has this kind of filtering as well as others.

--------------------
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
 


Custom Search


RSSSearch   Top   Lo-Fi    17th September 2019 - 03:52 PM