Help: Query for List Box in Access

[RDMRDM]

I have a list box in Access that I have set to allow mulitple choices. Wondering what I need to do to my query in order to pull back the data from the multiple fields. I want to be able to select multiple Lvl1_Organization records from the form. Previously it was a combo box and everything worked. Here is the query I used for the combo box.
Like [Forms]![Form_Distribution_Selection_Criteria]![Lvl1_Organization]

[ADezii]

I have a list box in Access that I have set to allow mulitple choices. Wondering what I need to do to my query in order to pull back the data from the multiple fields. I want to be able to select multiple Lvl1_Organization records from the form. Previously it was a combo box and everything worked. Here is the query I used for the combo box.
Like [Forms]![Form_Distribution_Selection_Criteria]![Lvl1_Organization]

Are you referring to multiple OR Criteria based on multiple selections for [Lvl1_Organization]? If you are, you will need to build a SQL String from items within the ItemsSelected collection. If you don't know how to do this, please reply and either myself or one of the other Moderators/Experts will assist you.

[RDMRDM]

Are you referring to multiple OR Criteria based on multiple selections for [Lvl1_Organization]? If you are, you will need to build a SQL String from items within the ItemsSelected collection. If you don't know how to do this, please reply and either myself or one of the other Moderators/Experts will assist you.

I am selectinging multiple fields on the form. For example, I may want to return all the records for HR, Operations, Admin. I would select these records in the list box, and it would return those records.

Here is the current SQL that is listed for this field in the query.
WHERE (((tbl_Master_Data.[Lvl 1 Org Description]) Like [Forms]![Form_Distribution_Selection_Criteria]![Lvl1_Organization]) AND ((tbl_Master_Data.[Lvl 2 Org Description]) Like [Forms]![Form_Distribution_Selection_Criteria]![Lvl2_Organization]) AND ((tbl_Master_Data.[Job category Description]) Like [Forms]![Form_Distribution_Selection_Criteria]![Job_Category]) AND ((tbl_Master_Data.[Employee Group Text]) Like [Forms]![Form_Distribution_Selection_Criteria]![Emp_Group]) AND ((tbl_Master_Data.[Personnel Area Text]) Like [Forms]![Form_Distribution_Selection_Criteria]![Personnel_Area]) AND ((tbl_Master_Data.[Personnel Subarea Text]) Like [Forms]![Form_Distribution_Selection_Criteria]![Personnel_SubArea]))
ORDER BY tbl_Master_Data.[Lvl 1 Org Description], tbl_Master_Data.[Lvl 2 Org Description], tbl_Master_Data.[Job category Description], tbl_Master_Data.[Employee Group Text], tbl_Master_Data.[Personnel Area Text], tbl_Master_Data.[Personnel Subarea Text];

[RDMRDM]

I am selectinging multiple fields on the form. For example, I may want to return all the records for HR, Operations, Admin. I would select these records in the list box, and it would return those records.

Here is the current SQL that is listed for this field in the query.
WHERE (((tbl_Master_Data.[Lvl 1 Org Description]) Like [Forms]![Form_Distribution_Selection_Criteria]![Lvl1_Organization]) AND ((tbl_Master_Data.[Lvl 2 Org Description]) Like [Forms]![Form_Distribution_Selection_Criteria]![Lvl2_Organization]) AND ((tbl_Master_Data.[Job category Description]) Like [Forms]![Form_Distribution_Selection_Criteria]![Job_Category]) AND ((tbl_Master_Data.[Employee Group Text]) Like [Forms]![Form_Distribution_Selection_Criteria]![Emp_Group]) AND ((tbl_Master_Data.[Personnel Area Text]) Like [Forms]![Form_Distribution_Selection_Criteria]![Personnel_Area]) AND ((tbl_Master_Data.[Personnel Subarea Text]) Like [Forms]![Form_Distribution_Selection_Criteria]![Personnel_SubArea]))
ORDER BY tbl_Master_Data.[Lvl 1 Org Description], tbl_Master_Data.[Lvl 2 Org Description], tbl_Master_Data.[Job category Description], tbl_Master_Data.[Employee Group Text], tbl_Master_Data.[Personnel Area Text], tbl_Master_Data.[Personnel Subarea Text];

Anyone have any suggestions on how to handle this "List Box"?

[ADezii]

Anyone have any suggestions on how to handle this "List Box"?

1. Let's assume your List Box is named lstOrganizations. You can call it whatever you like.
2. Set the Multi Select Property of this List Box to Extended.
3. Place this code block wherever appropriate.
4. NOTE: The code has been debugged and, in my opinion, is logically sound. It has not, however, been tested on actual data.
5. My friend, NeoPa is the real SQL Expert. Perhaps he can give you better guidance or offer a better approach to your problem. I'm sure he'll be stopping by to visit.
6. The intention of the code is to dynamically build the Criteria portion of an SQL String based on multiple selections within a List Box.

Dim ctl As Control, strSQL As String
Dim varItm As Variant, strBaseSQL As String
Dim strCompletedSQLString As String
 
Set ctl = Me!lstOrganizations
strBaseSQL = "Select * From <tblYourTable> Where <tblYourTable>.[Organization] IN("
 
If ctl.ItemsSelected.Count > 0 Then
  For Each varItm In ctl.ItemsSelected
    strSQL = strSQL & Chr$(34) & ctl.ItemData(varItm) & Chr$(34) & ","
  Next varItm
 
  strSQL = Left$(strSQL, Len(strSQL) - 1) & ")"
  strCompletedSQLString = strBaseSQL & strSQL
 
  Debug.Print strCompletedSQLString
End If

Typical OUTPUT:

Select * From <tblYourTable> Where <tblYourTable>.[Organization] IN("Org 2","Org 3","Org 5","Org 7","Org 10")

[NeoPa]

That all seems pretty sound.
Two minor points :

1. <tblYourTable> should probably take the form [tblYourTable] as it's possible that there may be spaces or other non-standard characters in a table name.
2. I would always recommend the use of (') as a quote char in SQL rather than (") (Chr$(34) in your code) as it's only Access converting it that makes it work. ANSI compliant servers would not recognise the (").

Other than that, the logic is as sound as a pound :)

[ADezii]

That all seems pretty sound.
Two minor points :

1. <tblYourTable> should probably take the form [tblYourTable] as it's possible that there may be spaces or other non-standard characters in a table name.
2. I would always recommend the use of (') as a quote char in SQL rather than (") (Chr$(34) in your code) as it's only Access converting it that makes it work. ANSI compliant servers would not recognise the (").

Other than that, the logic is as sound as a pound :)

Thanks for the Input, Neo. Your opinion is always appreciated and valued.