Filter split Form on Multiselect List Box

Dear Sir,

I am using below code for filtering some data. However, i wish to filter it by multi select from the List (ListPorts).
Kindly help and oblige.

Private Sub cmdSearch_Click()

Dim task As String
Dim varitem As Variant
   

    task = "SELECT ([tblpurchase].[pur_qty]-(select NZ(sum(tblsale.[sale_qty]),0) from tblsale where tblsale.[po_no] = tblpurchase.[PO_No])) AS Salable,"
    task = task + " Val((select  NZ(sum(tblsale.[sale_qty]),0) from tblsale where tblsale.[po_no] = tblpurchase.[PO_No])) AS Sale, "
    task = task + " Val((select NZ(sum(tbllifting.[Lift_qty]),0) from tbllifting where tbllifting.[po_no] = tblpurchase.[PO_No])) AS Lifted, "
    task = task + " Val((select NZ(sum([tblBillEntryFiled].[BE_QTY]),0) from tblBillEntryFiled where tblBillEntryFiled.[po_no] = tblpurchase.[PO_No])) AS BEFiled, "
    task = task + " ([tblpurchase].[pur_qty]-(select NZ(sum([tblLifting].[Lift_Qty]),0) from tbllifting where tbllifting.[po_no] = tblpurchase.[PO_No])) AS Net_Unlifted, "
    task = task + " Val((select NZ(sum([tblBillEntryFiled].[BE_QTY]),0) from tblBillEntryFiled where tblBillEntryFiled.[po_no] = tblpurchase.[PO_No]))-Val((select  NZ(sum(tblsale.[sale_qty]),0) from tblsale where [tblSale].[BT_Tax] = 'Tax' and tblsale.[po_no] = tblpurchase.[PO_No])) AS Tax_Stock, "
    task = task + " Val((select  NZ(sum(tblsale.[sale_qty]),0) from tblsale where [tblSale].[BT_Tax] = 'BT' and tblsale.[po_no] = tblpurchase.[PO_No])) AS BT_Sale, "
    task = task + " [pur_Qty]-[BT_Sale]-[BEFiled] AS BT_Stock, * "
    task = task + " FROM tblpurchase where "
   
    task = task + " [product] Like '*" & txtProduct & "*' and "
    task = task + " [Supplier] Like '*" & txtSupplier & "*' AND "
    task = task + " [PO_NO] Like '*" & txtPO & "*' AND "
    task = task + " [Broker] Like '*" & txtbroker2 & "*' AND "
   

    If chkSalable = True Then
        task = task + " ([tblpurchase].[pur_qty]-(select NZ(sum(tblsale.[sale_qty]),0) from tblsale where tblsale.[po_no] = tblpurchase.[PO_No])) > 0 and "
    End If
   
 ask = task + " [Storage_port]='" & ListPorts.Column(0) & "' "
   
   
   'For Each varitem In Me.ListPorts.ItemsSelected
     ' task = task & " [storage_port] = '" & Chr(34) & Me.ListPorts.ItemData(varitem) & Chr(34) & "'"
   'Next varitem

   
   
   
task = task & "Order by tblpurchase.PO_date desc"
   
   
   
   
     If Len(task) = 0 Then
      MsgBox "You did not select anything from the list" _
             , vbExclamation, "Nothing to find!"
      Exit Sub
   End If
       
Me.RecordSource = task
End Sub
Kamlesh JainManagerAsked:
Who is Participating?
 
Ryan ChongCommented:
try this instead:

task = task & " " & Storage_Ports & " Order by tblpurchase.PO_date desc"

Open in new window


you need to test scenarios when there were items selected as well as no item was selected
0
 
Ryan ChongCommented:
you can try use "IN" clause for this condition in case user selected multiple items.

'...
    
    Dim Storage_Ports As String
    
    For Each varitem In Me.ListPorts.ItemsSelected
        If Storage_Ports = "" Then
            Storage_Ports = "'" & Replace(Me.ListPorts.ItemData(varitem), "'", "''") & "'"
        Else
            Storage_Ports = Storage_Ports & ", '" & Replace(Me.ListPorts.ItemData(varitem), "'", "''") & "'"
        End If
    Next varitem
    If Storage_Ports <> "" Then
        Storage_Ports = " [storage_port] in (" & Storage_Ports & ") "
    End If
    MsgBox Storage_Ports
    
    '...

Open in new window

0
 
Dale FyeCommented:
So, what is the problem you are having?  Are you getting an error?

Assuming that the column, from the listbox, that you want to use in your criteria is the bound column, and that that column is text, then the syntax you are using is correct.  But this could create a really long criteria string.

Ryan has posted an option which will allow you to use an IN ( )  predicate instead of the = syntax you are currently using, which should significantly shorten the length of your criteria string.

Another way to handle this type of situation is to use a subform.  I generate a temporary table which contains a Yes/No field (IsSelected), along with the other data you are currently displaying in your listbox.  Then create a datasheet based on this temp table and use a checkbox for the Yes/No field.  Finally, in your query, I would do something like:

FROM yourTable
INNER JOIN tempTable on yourTable.StoragePort = tempTable.StoragePort
WHERE( (me.listports.ItemsSelected.Count = 0 ) OR (tempTable.IsSelected = True))

The first part of that WHERE clause basically says, if none of the items in the listbox are selected, include all of the StoragePort values.  If one or more of the values is selected, use only those that are selected.  An advantage of this is that evaluating the IN ( ) clause against a long list of values can be time consuming, whereas evaluating the value of a single field (tempTable.IsSelected) is very fast.

HTH
Dale
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Kamlesh JainManagerAuthor Commented:
Thanks Mr Ryan
I tried but getting error of 3075 (Missing operator) :(
Screen Shot is attached and hope the same shall be helpful in ascertaining my problem.
Best Regards,
2017-11-21.png
0
 
Dale FyeCommented:
Have you tried putting a break point in your code, and printing the SQL string (task) before you attempt to set the RecordSource or the filter?  This would allow you to copy the string into the SQL view of a query and run it from there.  Usually, when you do that, Access will actually highlight where the problem is occuring.
0
 
Kamlesh JainManagerAuthor Commented:
Yes sir. I will do that and assess the step by step error.
But prima facie, i guess the SQL is not reading the output of storage_ports variable.
I will revert again by the way.

Thanks :)
0
 
mbizupCommented:
Looks like you have a typo in there...

 ask = task + " [Storage_port]='" & ListPorts.Column(0) & "' "

Open in new window


Is that your actual code, or was the "t" (in task)  lost in the copy/paste?  It should be:

 task = task + " [Storage_port]='" & ListPorts.Column(0) & "' "

Open in new window


With the typo there, the Storage_Port criteria never gets added to the variable 'task', just to an undeclared variable 'ask'...
Which is a textbook example of why you should ALWAYS have Option Explicit at the top of your code modules, right under Option Compare Database, so that you are aware, at compile time of any undeclared variables (including typos).
0
 
Ryan ChongCommented:
and for String joining in Access VBA, always use & instead of + despite it may work too.

task = task & "bla bla..."
0
 
Kamlesh JainManagerAuthor Commented:
Sir,

1. Typo of Task and ask was just in copy paste only. Its proper in codes.
2. As suggested, i replaced all + with "&".
3. While going through it, msgbox shows both Storage_Ports value, however, it filters only the second Port, not both the ports. E.g., filtered data while selecting "Kandla " and "Hazira" ports from the list, the filter shows only records where storage_port = "Hazira".

I think i am missing something crucial in below code :
.........
task = task + " [Storage_port]='" & ListPorts.Column(0) & "' "
........

4. Below is  the breakup (extration only) of the SQL :

.................... [tblSale].[BT_Tax] = 'BT' and tblsale.[po_no] = tblpurchase.[PO_No])) AS BT_Sale,  [pur_Qty]-[BT_Sale]-[BEFiled] AS BT_Stock, *  FROM tblpurchase where  [product] Like '*edc*' and  [Supplier] Like '**' AND  [PO_NO] Like '**' AND  [Broker] Like '**' AND  [Storage_port]='Hazira'

5. Screen shot is attached for the condition.
Regards,
2017-11-22--2-.png
2017-11-22--2-.png
0
 
Kamlesh JainManagerAuthor Commented:
Dear Experts,
Your suggestions are awaited on above.
Regards,
Kamlesh Jain
0
 
Ryan ChongCommented:
can't really observe anything from your screenshots...

try this... before the line:

Me.RecordSource = task

Open in new window


try debug the generated SQL statement by adding:

debug.print task

Open in new window


let us know what you getting here?
0
 
Kamlesh JainManagerAuthor Commented:
Thanks.
Below is the outcome :

SELECT ([tblpurchase].[pur_qty]-(select NZ(sum(tblsale.[sale_qty]),0) from tblsale where tblsale.[po_no] = tblpurchase.[PO_No])) AS Salable, Val((select  NZ(sum(tblsale.[sale_qty]),0) from tblsale where tblsale.[po_no] = tblpurchase.[PO_No])) AS Sale,  Val((select NZ(sum(tbllifting.[Lift_qty]),0) from tbllifting where tbllifting.[po_no] = tblpurchase.[PO_No])) AS Lifted,  Val((select NZ(sum([tblBillEntryFiled].[BE_QTY]),0) from tblBillEntryFiled where tblBillEntryFiled.[po_no] = tblpurchase.[PO_No])) AS BEFiled,  ([tblpurchase].[pur_qty]-(select NZ(sum([tblLifting].[Lift_Qty]),0) from tbllifting where tbllifting.[po_no] = tblpurchase.[PO_No])) AS Net_Unlifted,  Val((select NZ(sum([tblBillEntryFiled].[BE_QTY]),0) from tblBillEntryFiled where tblBillEntryFiled.[po_no] = tblpurchase.[PO_No]))-Val((select  NZ(sum(tblsale.[sale_qty]),0) from tblsale where [tblSale].[BT_Tax] = 'Tax' and tblsale.[po_no] = tblpurchase.[PO_No])) AS Tax_Stock,  Val((select  NZ(sum(tblsale.[sale_qty]),0) from tblsale where [tblSale].[BT_Tax] = 'BT' and tblsale.[po_no] = tblpurchase.[PO_No])) AS BT_Sale,  [pur_Qty]-[BT_Sale]-[BEFiled] AS BT_Stock, *  FROM tblpurchase

where  [product] Like '*edc*' and  [Supplier] Like '**' AND  [PO_NO] Like '**' AND  [Broker] Like '**' AND  ([tblpurchase].[pur_qty]-(select NZ(sum(tblsale.[sale_qty]),0) from tblsale where tblsale.[po_no] = tblpurchase.[PO_No])) > 0 and  [Storage_port] = ' [storage_port] in ('Hazira', 'Kandla') Order by tblpurchase.PO_date desc

i tried below line for task :

task = task + " [Storage_port] = '" & Storage_Ports & "Order by tblpurchase.PO_date desc"

Thanks
0
 
Kamlesh JainManagerAuthor Commented:
Wow... Done !!! Thanks a million !!!! i was literally struggling.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.