Functions in queries

[Guest]

I'm trying to use the following function as criteria in a query, but I get no results

If I plug the results of the function into the query it works just fine, but I would like this to be dynamic. Any questions

Thank

Public Function CategoryFields(Separator As String) As Strin
Dim frm As For
Dim ctl As Contro
Dim intCurrentRow As Intege

Set frm = Form_frmDT
Set ctl = frm.lstSelCa

For intCurrentRow = 0 To frm.lstSelCat.ListCount -
CategoryFields = CategoryFields & Chr(34) & ctl.Column(0, intCurrentRow) & Chr(34) & Separato
Next intCurrentRo

CategoryFields = Left(CategoryFields, Len(CategoryFields) - Len(Separator)

Set ctl = Nothin
Set frm = Nothin

End Function

 

[Jeff Boyce]

So, what are you trying to do, not "how"? The function looks like you are
trying to build a string containing all selected values from a listbox.
I'll point out that listboxes often don't have the bound field and the
displayed field the same -- which are you after?

Next, when you use the function to build a string, then insert that string
as a criterion, Access tries to match the string. Are you looking for rows
where your field = "YourString", or where your field = any of the values IN
the string?

Take a look at the In() function -- you might be able to modify your
function to build a string that Access would treat as if you had entered
In("A", "B", ...) as the criterion.

Just a thought

Jeff Boyce
<Access MVP>