Query Criteria from list box

[maabbas]

Hi Everyone need your help one more time.

I have a parameter query where user enter the department number to get their info. I want instead of entering the department number, a list box pops up and ask to select department as parameter and run the same query.

any help will be appreciated.

 

[maabbas]

Hi Everyone need your help one more time.

I have a parameter query where user enter the department number to get their info. I want instead of entering the department number, a list box pops up and ask to select department as parameter and run the same query.

any help will be appreciated.

Sorry Everyone I was in hurry did not describe my question fully, also I search the internet and find some solution but still have one more question. so far I did following.

create 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 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 for 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]));

 

[Christos99]

Hi, i tried building a hidden text box containing the listbox selected items, then using IN from the query e.g In [Forms]![form1]![txtSelected]


However it doesn't recognise this and wants a real IN clause.
So your alternative is to build the query string when you need it, based on the list box and reset it in the actual query :

Dim db as database, qdf as querydef
Set db = currentdb
Set qdf = db.querydefs("MyQueryName")
qdf.SQL = "Select......... etc WHERE IssuedDepartment IN..... "

Loop through the list box items selected as below to build the IN clause. You could use = instead of IN.
Make sure you check or allow for none selected.

Option Compare Database
Option Explicit

    Dim varItem As Variant, strSelected as string

    strSelected = "("
    
    For Each varItem In List0.ItemsSelected
        Debug.Print List0.ItemData(varItem)
        If Len(strSelected) > 1 Then
            strSelected = strSelected & ","
        End If
        strSelected = strSelected & "'" & List0.ItemData(varItem) & "'"
    Next
    
    strSelected = strSelected & ")"