Issues using functions in a queries criteria;Can this be done?

[James.]

Hi All,
I have a query which simply selects all items from a table where they match
a function that is run in its criteria section using the "in" command.

The function, reads in from a seperate table, and takes each value read and
concats it to a string to be returned to the calling query to be used in the
criteria statment.

For some reason when the query runs, the function returns the desired info,
but the query qives me 0 results. I fear this is due to invalid syntax in
the Criteria line of the query or that it is just not possible in Access.

Any info on this would be awesome.

I have attached my function and query for your review:

Query:
SELECT TblSpecType.SpecName, TblSpecType.SpecDescription
FROM TblSpecType
WHERE (((TblSpecType.SpecName) In (GetRespSpec())));

Function:
Function GetRespSpec()

Dim sValue, sHolder, qry As String
Dim rst As Recordset
Dim db As Database


Set db = CurrentDb()
qry = "SELECT [RespDesc] FROM tblRespDesc"

Set rst = db.OpenRecordset(qry)
With rst
.MoveFirst
While Not .EOF
sHolder = ![RespDesc]
sValue = sValue + "'" + sHolder + "',"
.MoveNext
Wend
sValue = sValue + "blueprint'"

End With
GetRespSpec = sValue
rst.Close
End Function

 

[Douglas J Steele]

It's not possible to do that with an IN operator in Access.

What you can do, however, is to change GetRespSpec so that it puts a comma
at the beginning and end of the string, not just in the middle.

Then, change your WHERE clause to

WHERE InStr(GetRespSpec(), "," & TblSpecType.SpecName & ",") > 0

The reason for the commas at the beginning and end is to ensure that you
don't select Red when you actually wanted Redburn.

(Warning: It will be slow, especially for large tables....)