asked on

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

Ryan Chong

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

Dale Fye

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

Kamlesh Jain

ASKER

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

Dale Fye

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.

Kamlesh Jain

ASKER

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

mbizup

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

Ryan Chong

and for String joining in Access VBA, always use & instead of + despite it may work too.

task = task & "bla bla..."

Kamlesh Jain

ASKER

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

Kamlesh Jain

ASKER

Dear Experts,
Your suggestions are awaited on above.
Regards,
Kamlesh Jain

Ryan Chong

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?

Kamlesh Jain

ASKER

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

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kamlesh Jain

ASKER

Wow... Done !!! Thanks a million !!!! i was literally struggling.