Combobox value for multiple filter criteria in a Query (1 Viewer)

zulu100

Member
Local time
Today, 09:14
Joined
Nov 3, 2021
Messages
54
I have a combo-box on a Form based on a Query which I would like to filter by department. The actual filtering is done by an "Item Category" field. The Item category is usually a 5 figure value. The combo-box populate at hidden textbox on the form. This textbox is referenced in the filter criteria of the query.
This work fine if I limit the filter criteria to ONE 5 figure value like "32110" for example. However to find all item categories in one department I need to add multiple criteria. For example (Like "321*" Or Like "221*") When I add a "and", "or", "like", "*" it comes up with no results. Are there some limitations for this procedure or am I doing it the wrong way.??
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:14
Joined
May 7, 2009
Messages
19,169
you can only choose 1 item list from combobox.
why not try multiselect listbox?
 

zulu100

Member
Local time
Today, 09:14
Joined
Nov 3, 2021
Messages
54
I have attached the source for my combo-box. Only ID4 gives any result.
If I use the criteria directly in the Query then it works fine, for all ID´s
 

Attachments

  • cbmBox.png
    cbmBox.png
    10.7 KB · Views: 338

jdraw

Super Moderator
Staff member
Local time
Today, 04:14
Joined
Jan 23, 2006
Messages
15,364
Suggest you provide more details or a copy of the database. Readers need some context in order to offer focused advice.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:14
Joined
May 7, 2009
Messages
19,169
you need to add [fieldname] between those filters.
see this demo.
 

Attachments

  • combo_filtering.accdb
    480 KB · Views: 273

zulu100

Member
Local time
Today, 09:14
Joined
Nov 3, 2021
Messages
54
you need to add [fieldname] between those filters.
see this demo.
Thank you for the information
Maybe I am missing the point here. Please bare over with me :)
I have a form "frm_ctn_List" with Query "qry_PanelFilter" as record source. On the form is a combobox "cbm_DepartmentSelection" and at textbox "txt_DepartmentSelection". The textbox is passes the column value from the combo-box containing the filter expressions. See attached images.
The Textbox value is then referenced in the Query criteria and I have tried to add the field name, as mentioned, to the filter criteria expressions but it doesn't work.
I must be doing something wrong?
 

Attachments

  • Qry.png
    Qry.png
    3.3 KB · Views: 320
  • cbmBox1.png
    cbmBox1.png
    2.5 KB · Views: 341

zulu100

Member
Local time
Today, 09:14
Joined
Nov 3, 2021
Messages
54
you need to add [fieldname] between those filters.
see this demo.
I have tried to google for additional information and from that I have modified my search string. But still no luck. Any good ideas
Code:
[Item Category Code]Like """ & [Forms]![frm_ctn_List]![txt_DepartmentSelection] & "*"""
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:14
Joined
May 7, 2009
Messages
19,169
don't push too hard on one direction.
look for other solution if the one you have don't work.
 

zulu100

Member
Local time
Today, 09:14
Joined
Nov 3, 2021
Messages
54
Hmmm...
I have tried the switch function and added the like operator before. This works fine when only the filter is limited to one criteria.
Is it wrong to user OR if I want to add another criteria. If I do Access add like operators before both criteria in the end and nothing comes up in the query.
This works:
Code:
Like Switch([Forms]![frm_ctn_List]![txt_DepartmentSelection]=1;"212*")

But this doesn't:
Code:
Like Switch([Forms]![frm_ctn_List]![txt_DepartmentSelection]=1;Like "212*" Or Like "321*")
Obviously there are to many "like" here :)
I have up to 4 or 5 different like filter criteria I would like to add for each choice in my combobox.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:14
Joined
Feb 19, 2002
Messages
42,981
The problem is that a querydef can NOT be structurally modified with an argument. That is why the 32110 item work but not the others.

Like "321*" Or Like "221*" is being interperted as if the entire expression is a string:

Where somefield = 'Like "321*" Or Like "221*"' -- which is of course illogical.

You can do what you want but you would need to build the query in VBA. You also need to add the "=" to your single value entries.

Dim strSQL as String
strSQL = "Select ... From ... Where " & Me.combo
Then set the RecordSource = strSQL
 

zulu100

Member
Local time
Today, 09:14
Joined
Nov 3, 2021
Messages
54
The problem is that a querydef can NOT be structurally modified with an argument. That is why the 32110 item work but not the others.

Like "321*" Or Like "221*" is being interperted as if the entire expression is a string:

Where somefield = 'Like "321*" Or Like "221*"' -- which is of course illogical.

You can do what you want but you would need to build the query in VBA. You also need to add the "=" to your single value entries.

Dim strSQL as String
strSQL = "Select ... From ... Where " & Me.combo
Then set the RecordSource = strSQL
Ok - Thank you for pointing me in the right direction.
 

zulu100

Member
Local time
Today, 09:14
Joined
Nov 3, 2021
Messages
54
You're welcome:)


I had some time to look at this again and I made a small sample where I try filter by type and there are more than one criteria.
Value 1 in cbm box should filter "Nummer" by 1 and 6 to show all kitchen elements and so on.
But something is wrong in the SQL filter expression.
I hava attached the error and the sample database
Any ideas ?
Thanks.


Code:
Private Sub cbm_AfterUpdate()
    
    Dim strSQL As String
    
    strSQL = "SELECT tbl_produkter.ID, tbl_produkter.Nummer, tbl_produkter.Produkt FROM tbl_produkter WHERE (((tbl_produkter.Nummer) IN "
    If cbm.Value = 1 Then
    strSQL = strSQL & "('1','6')"
    ElseIf cbm.Value = 2 Then
    strSQL = strSQL & "('2','5')"
    ElseIf cbm.Value = 3 Then
    strSQL = strSQL & "('3','7')"
    Else: strSQL = strSQL & "('4','8')"
    End If
    Me.RecordSource = strSQL
      
    
End Sub
 

Attachments

  • Database_2.zip
    113 KB · Views: 291
  • Error.png
    Error.png
    20.4 KB · Views: 271

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:14
Joined
May 7, 2009
Messages
19,169
see your form.
 

Attachments

  • Database_2.zip
    123.6 KB · Views: 216

zulu100

Member
Local time
Today, 09:14
Joined
Nov 3, 2021
Messages
54
Thanks a lot... You are fast :)
This i just the functionality I am looking for.
However this is just a part of a Query. I need to put the rest of the SQL into the VBA Code. Or maybe divide it
Would that be possible??
Your code replaces the switch function.


Code:
SELECT [dbo_KonfAir DRIFT$Sales Header].[Bill-to Name], [dbo_KonfAir DRIFT$Item].[Item Category Code], dbo_x_Production_View.Prod_Ordrenr_, [dbo_KonfAir DRIFT$Production Order].No_, dbo_x_Production_View.Salgsordrenr_, tbl_Status.Start, [dbo_KonfAir DRIFT$Item$db97a7b0-945f-4089-8891-b9dd5651c769].Quality, tbl_Status.Status_Status, [dbo_KonfAir DRIFT$Production Order].Status, [dbo_KonfAir DRIFT$Production Order].Quantity AS Nummer, (Val([Nummer])) AS Antal, [dbo_KonfAir DRIFT$Production Order].[Ending Date] AS [Slut Dato], [dbo_KonfAir DRIFT$Production Order].[Source No_] AS [Vare nummer], [dbo_KonfAir DRIFT$Item].Description AS Beskrivelse, [dbo_KonfAir DRIFT$Production Order].[Location Code] AS Lokationskode, tbl_Status.Medarbejder_1Numeric, tbl_Status.Medarbejder_2Numeric, tbl_Status.Medarbejder_3Numeric, tbl_Status.Medarbejder_4Numeric, tbl_Status.Medarbejder_5Numeric, tbl_Status.Medarbejder_6Numeric, tbl_Status.Medarbejder_7Numeric, tbl_Status.Medarbejder_8Numeric, tbl_Status.Medarbejder_9Numeric, tbl_Status.Medarbejder_10Numeric, tbl_Status.Medarbejder_11Numeric, tbl_Status.Medarbejder_12Numeric, tbl_Status.Medarbejder_13Numeric, tbl_Status.Medarbejder_14Numeric, dbo_x_Production_View.Planlagt_afsend
FROM ((([dbo_KonfAir DRIFT$Production Order] INNER JOIN tbl_Status ON [dbo_KonfAir DRIFT$Production Order].No_ = tbl_Status.StatusID) INNER JOIN ([dbo_KonfAir DRIFT$Item] INNER JOIN [dbo_KonfAir DRIFT$Item$db97a7b0-945f-4089-8891-b9dd5651c769] ON [dbo_KonfAir DRIFT$Item].No_ = [dbo_KonfAir DRIFT$Item$db97a7b0-945f-4089-8891-b9dd5651c769].No_) ON [dbo_KonfAir DRIFT$Production Order].[Source No_] = [dbo_KonfAir DRIFT$Item].No_) LEFT JOIN dbo_x_Production_View ON [dbo_KonfAir DRIFT$Production Order].No_ = dbo_x_Production_View.Prod_Ordrenr_) LEFT JOIN [dbo_KonfAir DRIFT$Sales Header] ON dbo_x_Production_View.Salgsordrenr_ = [dbo_KonfAir DRIFT$Sales Header].No_
WHERE ((([dbo_KonfAir DRIFT$Item].[Item Category Code]) Like Switch([Forms]![frm_ctn_List]![txt_DepartmentSelection]=3,"32[2]*",[Forms]![frm_ctn_List]![txt_DepartmentSelection]=4,"32[1]*",[Forms]![frm_ctn_List]![txt_DepartmentSelection]=1,"31[4]*",[Forms]![frm_ctn_List]![txt_DepartmentSelection]=2,"31[2]*",[Forms]![frm_ctn_List]![txt_DepartmentSelection]=5,"2121*")) AND ((tbl_Status.Status_Status)=0) AND (([dbo_KonfAir DRIFT$Production Order].Status)=3) AND (([dbo_KonfAir DRIFT$Production Order].[Location Code])=IIf([Forms]![frm_ctn_List]![txt_CountrySelection]=1,"DK","LIT")))
ORDER BY [dbo_KonfAir DRIFT$Production Order].[Ending Date];
 

zulu100

Member
Local time
Today, 09:14
Joined
Nov 3, 2021
Messages
54
see your form.
Hi again

I have tried to insert the code into my SQL, but I get an error after the "Where" clause. I can see the issue in appending "And" after the "Where" clause but do not know how the syntax should be.


Code:
Private Sub cbm_DepartmentSelection_AfterUpdate()



strSQL = "SELECT [dbo_KonfAir DRIFT$Sales Header].[Bill-to Name], [dbo_KonfAir DRIFT$Item].[Item Category Code], " & _
    " dbo_x_Production_View.Prod_Ordrenr_, [dbo_KonfAir DRIFT$Production Order].No_, " & _
    " dbo_x_Production_View.Salgsordrenr_, tbl_Status.Start, " & _
    " [dbo_KonfAir DRIFT$Item$db97a7b0-945f-4089-8891-b9dd5651c769].Quality, tbl_Status.Status_Status, " & _
    " [dbo_KonfAir DRIFT$Production Order].Status, [dbo_KonfAir DRIFT$Production Order].Quantity AS Nummer, " & _
    " (Val([Nummer])) AS Antal, [dbo_KonfAir DRIFT$Production Order].[Ending Date] AS [Slut Dato], " & _
    " [dbo_KonfAir DRIFT$Production Order].[Source No_] AS [Vare nummer], " & _
    " [dbo_KonfAir DRIFT$Item].Description AS Beskrivelse, " & _
    " [dbo_KonfAir DRIFT$Production Order].[Location Code] AS Lokationskode, " & _
    " tbl_Status.Medarbejder_1Numeric, tbl_Status.Medarbejder_2Numeric, " & _
    " tbl_Status.Medarbejder_3Numeric, tbl_Status.Medarbejder_4Numeric, tbl_Status.Medarbejder_5Numeric, " & _
    " tbl_Status.Medarbejder_6Numeric, tbl_Status.Medarbejder_7Numeric, tbl_Status.Medarbejder_8Numeric, " & _
    " tbl_Status.Medarbejder_9Numeric, tbl_Status.Medarbejder_10Numeric, tbl_Status.Medarbejder_11Numeric, " & _
    " tbl_Status.Medarbejder_12Numeric, tbl_Status.Medarbejder_13Numeric, " & _
    " tbl_Status.Medarbejder_14Numeric, dbo_x_Production_View.Planlagt_afsend " & _
    " FROM ((([dbo_KonfAir DRIFT$Production Order] " & _
    " INNER JOIN tbl_Status ON [dbo_KonfAir DRIFT$Production Order].No_ = tbl_Status.StatusID) " & _
    " INNER JOIN ([dbo_KonfAir DRIFT$Item] " & _
    " INNER JOIN [dbo_KonfAir DRIFT$Item$db97a7b0-945f-4089-8891-b9dd5651c769] " & _
        " ON [dbo_KonfAir DRIFT$Item].No_ = [dbo_KonfAir DRIFT$Item$db97a7b0-945f-4089-8891-b9dd5651c769].No_) " & _
        " ON [dbo_KonfAir DRIFT$Production Order].[Source No_] = [dbo_KonfAir DRIFT$Item].No_) " & _
    " LEFT JOIN dbo_x_Production_View " & _
        " ON [dbo_KonfAir DRIFT$Production Order].No_ = dbo_x_Production_View.Prod_Ordrenr_) " & _
    " LEFT JOIN [dbo_KonfAir DRIFT$Sales Header] " & _
        " ON dbo_x_Production_View.Salgsordrenr_ = [dbo_KonfAir DRIFT$Sales Header].No_ "
    If Me.cbm_DepartmentSelection.ListIndex <> -1 Then
        strSQL = strSQL & _
        " WHERE ([dbo_KonfAir DRIFT$Item].[Item Category Code]) IN (" & Choose(Me.cbm_DepartmentSelection, "31410,31420", "31210,31220", "32210,32220", "32100,32110", "2121") & ");"
        End If
        " AND ((tbl_Status.Status_Status)=0) " & _
        " AND (([dbo_KonfAir DRIFT$Production Order].Status)=3) " & _
        " AND (([dbo_KonfAir DRIFT$Production Order].[Location Code])=IIf([Forms]![frm_ctn_List]![txt_CountrySelection]=1,"DK","LIT"))) " & _
    " ORDER BY [dbo_KonfAir DRIFT$Production Order].[Ending Date];"
    Me.RecordSource = strSQL

Me.Requery

End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:14
Joined
May 7, 2009
Messages
19,169
you try this:
Code:
Private Sub cbm_DepartmentSelection_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT [dbo_KonfAir DRIFT$Sales Header].[Bill-to Name], [dbo_KonfAir DRIFT$Item].[Item Category Code], " & _
    " dbo_x_Production_View.Prod_Ordrenr_, [dbo_KonfAir DRIFT$Production Order].No_, " & _
    " dbo_x_Production_View.Salgsordrenr_, tbl_Status.Start, " & _
    " [dbo_KonfAir DRIFT$Item$db97a7b0-945f-4089-8891-b9dd5651c769].Quality, tbl_Status.Status_Status, " & _
    " [dbo_KonfAir DRIFT$Production Order].Status, [dbo_KonfAir DRIFT$Production Order].Quantity AS Nummer, " & _
    " (Val([Nummer])) AS Antal, [dbo_KonfAir DRIFT$Production Order].[Ending Date] AS [Slut Dato], " & _
    " [dbo_KonfAir DRIFT$Production Order].[Source No_] AS [Vare nummer], " & _
    " [dbo_KonfAir DRIFT$Item].Description AS Beskrivelse, " & _
    " [dbo_KonfAir DRIFT$Production Order].[Location Code] AS Lokationskode, " & _
    " tbl_Status.Medarbejder_1Numeric, tbl_Status.Medarbejder_2Numeric, " & _
    " tbl_Status.Medarbejder_3Numeric, tbl_Status.Medarbejder_4Numeric, tbl_Status.Medarbejder_5Numeric, " & _
    " tbl_Status.Medarbejder_6Numeric, tbl_Status.Medarbejder_7Numeric, tbl_Status.Medarbejder_8Numeric, " & _
    " tbl_Status.Medarbejder_9Numeric, tbl_Status.Medarbejder_10Numeric, tbl_Status.Medarbejder_11Numeric, " & _
    " tbl_Status.Medarbejder_12Numeric, tbl_Status.Medarbejder_13Numeric, " & _
    " tbl_Status.Medarbejder_14Numeric, dbo_x_Production_View.Planlagt_afsend " & _
    " FROM ((([dbo_KonfAir DRIFT$Production Order] " & _
    " INNER JOIN tbl_Status ON [dbo_KonfAir DRIFT$Production Order].No_ = tbl_Status.StatusID) " & _
    " INNER JOIN ([dbo_KonfAir DRIFT$Item] " & _
    " INNER JOIN [dbo_KonfAir DRIFT$Item$db97a7b0-945f-4089-8891-b9dd5651c769] " & _
        " ON [dbo_KonfAir DRIFT$Item].No_ = [dbo_KonfAir DRIFT$Item$db97a7b0-945f-4089-8891-b9dd5651c769].No_) " & _
        " ON [dbo_KonfAir DRIFT$Production Order].[Source No_] = [dbo_KonfAir DRIFT$Item].No_) " & _
    " LEFT JOIN dbo_x_Production_View " & _
        " ON [dbo_KonfAir DRIFT$Production Order].No_ = dbo_x_Production_View.Prod_Ordrenr_) " & _
    " LEFT JOIN [dbo_KonfAir DRIFT$Sales Header] " & _
        " ON dbo_x_Production_View.Salgsordrenr_ = [dbo_KonfAir DRIFT$Sales Header].No_ "

strSQL = strSQL & _
        " WHERE ((tbl_Status.Status_Status)=0) " & _
        " AND (([dbo_KonfAir DRIFT$Production Order].Status)=3) " & _
        " AND (([dbo_KonfAir DRIFT$Production Order].[Location Code])=IIf([Forms]![frm_ctn_List]![txt_CountrySelection]=1,'DK','LIT'))) "

    
    If Me.cbm_DepartmentSelection.ListIndex <> -1 Then
        strSQL = strSQL & _
        " AND ([dbo_KonfAir DRIFT$Item].[Item Category Code]) IN (" & Choose(Me.cbm_DepartmentSelection, "31410,31420", "31210,31220", "32210,32220", "32100,32110", "2121") & " "
    End If
    
strSQL = strSQL & "ORDER BY [dbo_KonfAir DRIFT$Production Order].[Ending Date];"
    Me.RecordSource = strSQL

Me.Requery

End Sub
 

zulu100

Member
Local time
Today, 09:14
Joined
Nov 3, 2021
Messages
54
Thanks :)
Isn't the record source suppose to be set to strSQL, like the attached image.
I get an error that the record source doesn't exists
 

Attachments

  • error2.png
    error2.png
    48.6 KB · Views: 274
  • Error.png
    Error.png
    41.1 KB · Views: 274

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:14
Joined
May 7, 2009
Messages
19,169
no, its not we are setting it in VBA.
where did you get the string SQL (i supposed you created a Query first).
use that query as your recordsource of your form first.
 

zulu100

Member
Local time
Today, 09:14
Joined
Nov 3, 2021
Messages
54
Do I need to put the tables and fields inside the Query builder for the form to find the record source ?

Yes... it was first created in a query and initially used as record source. Now, if I change it to strSQL then I get the error "Query must have at least one destination field"
 
Last edited:

Users who are viewing this thread

Top Bottom