Can I call a function from a criteria line of a query using "In"

[James D.]

I can't seem to get this to work and am getting a bit frustrated. I have a
query that calls a function in the criteria field(See below for Query syntax).

The functions job is to read in from a different table, take each value read
and append it to a string variable, and return that variable to the query to
be used with the "IN" keyword.

Can I even do this? I have done this with the "Like" command but I cant
seem to get this to work using "In". Any Ideas would be greatly
appreciated.

Every time I run it I get 0 Records, when I should be getting over 40.
Attached is my code for the function and the query:

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

I have the followign Function GetRespSpec() which is designed to read in
from a table, and create a long concat string variable to be used in the "In"
comand.

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

 

[Albert D.Kallal]

You can do as you ask, but you have to form a CORRECT sql statement...

Where lastname = getlastname()

In the above, are we looking for a person with a lastname called
getlastname(). That indeed would be a very weird lastname.

How about we make a function called Albert?

Where FirstName = Albert

how do we distinguish between looking for a name, and that of a values
returned by a function?

Thus, you need to BUILD the correct sql...


eg:

strSql = "SELECT SpecName, SpecDescription FROM TblSpecType" & _
" WHERE SpecName In ('Red', 'Green', 'blue')"

note the above syntax.

So, your function needs to return a list of values, each separated by a
comma, and each value surrounded by quotes...

strSql = "SELECT SpecName, SpecDescription FROM TblSpecType" & _
" WHERE SpecName In (" & GetRespSpec() & ")"

Set rst = currentdb.OpenRecordset(strSql)

The above should work IF YOUR GetRespSpec retuns the list of values
SEPERATED by a comma!!

Note how the above code surroundes the result with "(" and a ")".

On the other hand, why bother writing code...why not just use a sub
query......

strSql = "SELECT SpecName, SpecDescription FROM TblSpecType" & _
" WHERE SpecName In (select RespDesc FROM tblRespDesc)

The above does the same thing as your code anyway.....

 

[James D.]

First of all Thanks Albert, your great.

your response got me thinking and I was able to get the desired result by
using a JOIN instead of the function.

I understand what you mean when you wrote out in code:
strSql = "SELECT SpecName, SpecDescription FROM TblSpecType" & _
" WHERE SpecName In (" & GetRespSpec() & ")"

But I am unsure how to actually write this in the design view of Access.
Every time a try I get the following syntax:

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

The function runs fine, but for some reason I guess the returned value is
not evaluated properly by the criteria for some reason. Any suggestions
would be great as I am sure I will run into this issue again?

Thanks again for all of your help.

 

[Albert D.Kallal]

But I am unsure how to actually write this in the design view of Access.

Every time a try I get the following syntax:

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

you can't use the query builder in this case. you HAVE to use code, since
there is no provisions in the query builder for substituting strings of
text...

Why not suff the whole sql into the above...

SELECT GetRespSpec()

The above will not work....

I can't have a expression like

GetMyFields = "FirstName, LastName, Company from tblCustomers"

select GetMyFields from tblCustomers

The above will not work. So, you can NOT use a function to replace the
actual sql string....

If you could, then why even bother writing the sql....

GetMyFields = "select FirstName, LastName, Company from tblCustomers"

now, what is your sql statement going to look like

Getmyfields ????

you can't even go

select GetField1(), GetField2(), GetField3() from tblCustomers

Your functions NEVER REPLACE ACTUAL SQL CODE!!!! you can ONLY use a
function to replace a expression value in the sql....


So, I can go

select LastName, firstName, Company where lastname = 'Ablert'
or

select LastName, firstName, Company where lastname = GetLastName()

That expression Albert can be replaced with a function...or

select LastName, firstName, Company
where lastname in ('Ablert', 'David','bob')

So, each of the 3 above expressions 'albert', 'david'..etc can EACH be
replaced
with a function, but not parts of the sql itself....

select LastName, firstName, Company
where lastname in (Function1(), Function2(), Function3())

note how the comma, and brackets are part of the sql..and we ONLY replace
actual expression values in the sql...NEVER the actual sql is replaced, or
modified....

So, you have to build the sql in code...or use the simply sub-query example
I gave you, since you don't need any code at all in that case...
(not sure why you resorted to a JOIN, but the sub query should do the trick
for you).

 

[James D.]

Perfect, thanks for the clarification, Its exactly what I needed to know.