Query probelm

[Gil D.]

Hello,

I have a form with sitesNames listbox.
I have a function which returns a list of choosen values:

Public Function GetChoosenSitesIndexForReports() As String
Dim lst As ListBox
Dim vItem As Variant
Dim sWhere As String
Dim iLen As Integer

Set lst = Forms!MonthlyReport!sitesNames

' Build string by looping through selected items.
For Each vItem In lst.ItemsSelected
If Not IsNull(vItem) Then
sWhere = sWhere & lst.ItemData(vItem) & ","
End If
Next

' Cut off the trailing comma
iLen = Len(sWhere) - 1
If iLen > 0 Then
sWhere = Left$(sWhere, iLen)
End If

GetChoosenSitesIndexForReports = sWhere

End Function


Function retured value can be: 1 or can be 1,2 etc.

I have a query in which siteIndex criteria is: IN
(GetChoosenSitesIndexForReports()).

My query works fine when returned value contains one number like: 1
but when it contains more numbers like: 1,2 it does not work.
If I set criteria in query builder to be: 1,2 (I mean: without using my
function) it works.

How can I base my query on returend string from my function ?

Thank you
Gil D.

 

[Duane Hookom]

You were doing well until you called the function in the criteria of your
query. It won't work like that.

Consider the generic listbox function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[Gil D.]

Hello,

Thank you, you solved my porblem.

Gil D.