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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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 FyeOwner, Developing Solutions LLCCommented:
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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Dale FyeOwner, Developing Solutions LLCCommented:
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kamlesh JainManagerAuthor Commented:
Wow... Done !!! Thanks a million !!!! i was literally struggling.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.