Multi-Select List Box as Query Parameter

[3239]

Hello everyone,

The code I have below works if the arguments are passed to the query as text. My question is how do I pass arguments to the query as a numbers?

Thanks,

[green]' Declare variables[/green]
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
    
[green]' Get the database and stored query[/green]
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("QryData")
    
[green]' Loop through the selected items in the list box and build a text string[/green]
    If Me!xRequisition.ItemsSelected.Count > 0 Then
        For Each varItem In Me!xRequisition.ItemsSelected
            strCriteria = strCriteria & " RequisitionLineItems.BudgetUnit = " & Chr(34) _
                          & Me!xRequisition.ItemData(varItem) & Chr(34) & "OR "
        Next varItem
        strCriteria = left(strCriteria, Len(strCriteria) - 3)
    Else
        strCriteria = "RequisitionLineItems.BudgetUnit Like '*'"
    End If
    
[green]' Build the new SQL statement incorporating the string[/green]
  
strSQL = "SELECT DISTINCT Requisition.RequisitionNumber, " & _
"PurchaseOrder.POID, Requisition.SupplierName, PurchaseOrder.Date, " & _
"RequisitionLineItems.BudgetUnit, RequisitionLineItems.POChartOfAccount " & _
"FROM (PurchaseOrder INNER JOIN Requisition ON PurchaseOrder.POID = " & _
"Requisition.POID) INNER JOIN RequisitionLineItems ON Requisition.RequisitionNumber = " & _
"RequisitionLineItems.RequisitionNumber " & _
"IN '\\Svr-fp-rh3\groups\Purchasing Tracking Database Systems\IS Procurement Test\IS Procurement Project Test_be.mdb'" & _
"WHERE " & strCriteria & ";"

[green]' Apply the new SQL statement to the query[/green]
    qdf.SQL = strSQL

[green]' Open the query[/green]
    DoCmd.OpenQuery "QryData"

[green]' Empty the memory[/green]
    Set db = Nothing
    Set qdf = Nothing

 

[SkipVought]

Hi,

For a LIST, use the IN operator.

for numeric...

Where [FieldName] IN ([i]val1,val2,...,valn[/i])

for string...

Where [FieldName] IN ([i]'val1','val2',...,'valn'[/i])

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}