Please Help for Passing Multiple Select ListBox Values as Parameter for WHERE IN clause of Query

[SoLost1]

I'm brand new to Access so I may be doing this completely wrong, but I have a separate query object I wrote and I am trying to only include records based on the deals that were multiselected on the form

SELECT tblIRRCashflow.DealName, tblIRRCashflow.Payment, vtblIRRCashflow.IRRDate
FROM tblIRRCashflow
WHERE tblIRRCashflow.DealID IN (arSelected);

On the form I have a button on click it is running the public function below:

Public Function ListSelectValue()
Dim varItem As Variant
Dim strSearch As String
Dim qrtext As String
Dim Task() As Double
Dim arSelected

For Each varItem In Forms!frmPerfTool!tboDeal.ItemsSelected
    strSearch = strSearch & "," & Forms!frmPerfTool!tboDeal.ItemData(varItem)
Next varItem
If Len(strSearch) = 0 Then
Else
    strSearch = Right(strSearch, Len(strSearch) - 1)
arSelected = Split(strSearch, ",")
End If
End Function

It doesn't return any records. I've tried using the strSearch as a string in the IN() of the query, but that doesn't seem to work and when I run the query it just prompts me to enter a parameter for strSearch. I don't even know if I'm on the right path with the WHERE IN or if there is a better way to be doing this. Should I be writing/running the SQL query from the VBA instead of a separate object? If so, how do I embed the results of that query in a subform/subreport control of the form? Help PLEAAAAASE. Thanks.

 

[SkipVought]

Hi,

Your IN ought to be...
[tt]
"'Value1','Value2'...,'Valuen'"
[/tt]

Public Function [b]ListSelectValue[/b]()
Dim varItem As Variant

For Each varItem In Forms!frmPerfTool!tboDeal.ItemsSelected
    [b]ListSelectValue[/b] = [b]ListSelectValue[/b] & "[b]'[/b]" & Forms!frmPerfTool!tboDeal.ItemData(varItem) & "[b]',[/b]"
Next varItem
If Len([b]ListSelectValue[/b]) = 0 Then
Else
    [b]ListSelectValue[/b] = Left([b]ListSelectValue[/b], Len([b]ListSelectValue[/b]) - 1)
[s][b]ListSelectValue[/b] = Split(strSearch, ",")[/s]
End If
End Function

SELECT tblIRRCashflow.DealName, tblIRRCashflow.Payment, vtblIRRCashflow.IRRDate
FROM tblIRRCashflow
WHERE tblIRRCashflow.DealID IN ([b]ListSelectValue[/b]);

I think I got everything.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

Since you are going after just one table, you may as well do just this:

SELECT DealName, Payment, IRRDate
FROM tblIRRCashflow
WHERE DealID IN (ListSelectValue);

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

You can't use any multivalue expression in a query. You need to update the SQL statement of your saved query. There are a couple of examples in the FAQs of this forum.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016