G
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.
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.