User defined function in statement (Access97)

[RV]

Hello,

I've created a user defined function to get all the values which are selected from a
MultiSelect listbox.
The function returns a string containing all values.
The function itself works OK.

I try to refer to the function in an SQL statement in a WHERE clause:

SELECT somecolumns
FROM sometables
WHERE table1.ActivityNumber IN (ListBoxActivities());

being ListBoxActivities the function I would like to refer to.

After one or more items are selected in the listbox, the query is run by clicking on a button.
As result no rows are selected.

I guess the reason why no records are selected is in the function itself, returning a string as result.
I can't figure out how to solve this.
Here's the code used to create define the
function:

Option Compare Database
Option Explicit
Public strValuelist As String

Public Function ListBoxActivities() As String

Dim dbs As Database
Dim varItem As Variant
Dim lst As Listbox

Set lst= Forms![AutomaticRemittance]!Activities
Set dbs = CurrentDb

If lst.ItemsSelected.Count > 0 Then
With lst
For Each varItem In lst.ItemsSelected
strValuelist = strValuelist & "," & lst.ItemData(varItem)
Next varItem
End With
strValuelist = Mid(strValuelist, 2)
End If
Set dbs = Nothing

What's going wrong here?

RV

 

[Rich]

Try something like
Private Sub cmdRunReport_Click()
On Error GoTo Err_cmdRunReport_Click
Dim MyDB As DATABASE
Dim I As Integer, strSQL As String
Dim strWhere As String, strIN As String
Dim flgAll As Boolean

Set MyDB = CurrentDb()
Select Case Me.optVal
Case Is = 1
strSQL = "INSERT INTO tblMail ( fldLtrPK, CustID )SELECT tblLetters.fldLtrPK, Addresses.CustID FROM Addresses, tblLetters "




'create the IN string by looping thru the listbox
For I = 0 To List0.ListCount - 1
If List0.Selected(I) Then
If List0.Column(0, I) = "All" Then
flgAll = True
End If
strIN = strIN & Chr(34) & List0.Column(2, I) & Chr(34) & ","
End If
Next I

'create the WHERE string, stripping off the last comma of the IN string
strWhere = "WHERE [LastName] In (" & left((strIN), Len(strIN) - 1) & " ) And ([tblLetters].[fldLtrPK] In(" & Me![Text7] & "))"

'if "All" was selected, don't add the WHERE condition
If Not flgAll Then
strSQL = strSQL & strWhere
End If

DBEngine(0)(0).Execute strSQL, dbFailOnError

 

[RV]

Rich,

thanks for your reply.
Just wondering, is it possible at all to use the function in a query itself?
I'm not to fond about using hardcoded SQL statements (makes maintainance / re-use rather complicated).

Greetings,

RV

 

[Rich]

Haven't tried this but is it something like
SELECT somecolumns
FROM sometables
WHERE table1.ActivityNumber = ListBoxActivities()

 

[RV]

Rich,

I've tried this before, no success.
The query containing the function in the WHERE clause runs with no errors, but the query returns no records to be retrieved.
I've tested the query using the Activity
Numbers corresponding to the values selected in the listbox. It works just fine.

I guess it's got something to do with the way the corresponding Activity Numbers are concatenated in a string through the function.

I'd really appreciate further assistance.

Greetings,

RV