Query Criteria from list box

[maabbas]

Hello Everyone,

Once again its me and with a new question, before asking question just to say, recently got promotion and some of the credit goes to this forum.

here is my question. I have a parameter query where user enter the criteria and get their result. I want to use a list box where user can select the value and get their result. so far I did the following

created a form (frmCriteria), with list box link to table, in query criteria I added the [Forms]![frmCriteria]!
[List12].
Its works perfectly when I choose one value, but does not work when I change the list box properties, Multi Select from none to Extended, is there any way I can select multiple value or select all value from list box and query display the result. The SQL statement of my query as follows.

SELECT tblInventoryTransaction.IssuedDepartment, tblDepartment.Description, tblInventoryTransaction.TransactionDate, tblInventoryTransaction.TransactionItem, tblInventoryTransaction.Quantity
FROM tblDepartment INNER JOIN tblInventoryTransaction ON tblDepartment.ID = tblInventoryTransaction.IssuedDepartment
WHERE (((tblInventoryTransaction.IssuedDepartment)=[Forms]![frmCriteria]!
[List12]));

Thanks y'all in advance

 

[TerryHogarth21]

You cannot pass a multi list box value into the parameter of a query based on research.

Have a read below as an alternative.
Microsoft Access tips: Use a multi-select list box to filter a report

What I ended up doing following Allen's example is create a report based on a select query without anything in the where clause - something like

 SELECT * FROM Table1

On a Form I have a button with the name Buttonz and the below code in the on Click Event.

Private Sub Buttonz_Click()

'Declare variable
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
Dim sSQL As String
Dim strDoc As String

'Name of Report
strDoc = "FX_Report"

'make sure a selection has been made
If Me.ListAll.ItemsSelected.Count = 0 Then
  MsgBox "You must pick an item"
  Exit Sub
End If

'add selected values to string
Set ctl = Me.ListAll
For Each varItem In ctl.ItemsSelected

  'strWhere = strWhere & ctl.ItemData(varItem) & ","
  'Use this line if your value is text
  strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem

'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)


''    'You cannot use values in a multi-selected list box to filter a query
''    sSQL = "SELECT Currency_Name, Currency_Rate FROM FX_Table WHERE Currency_Name IN(" & strWhere & ");"
''     Debug.Print sSQL
''
''     'Executes the SQL Command
''     DoCmd.RunSQL (sSQL)

       'You can however get these values and utilize them to filter a report

'open the report, restricted to the selected items
 DoCmd.OpenReport strDoc, acPreview, , "Currency_Name IN(" & strWhere & ")"

End Sub

On the comment out of trying to literally creating the SQL string to make the query it'll error out as the multiple selected data doesn't function with a query.
When I debug it, I could literally grab the SQL and paste it into a query and it'll run, but not run if I build the SQL string using the multi selected listbox as a parameter in the WHERE clause.

I since then followed what the above website link had and was able to filter the report based on the multi selected values. Which kind of is another step added onto what you already had.

 

[maabbas]

Thanks Terry I will give a shot and let you know