using a function to build the WHERE clause of a query

[ChasW]

I have a function that creates a custom list of values.
I want the values to be usable from a query such as this one.

SELECT *
FROM tbl
WHERE (((tbl.field) In ("value1", "value2")));

so of course i tried:

SELECT *
FROM tbl
WHERE (((tbl.field) In (myFunction())));

which of course only works for a list with 1 value in it, but if the
function returns a string such as 'A', 'B', 'C'

IN() still sees "'A', 'B', 'C'"
i.e. a single value

so.. what are my options here.
Can the function type be a table?

I dont really need to use IN, but I just showed that here as one of
the examples that I have tried and it didn't work.

I should mention that I saw this:
http://www.mvps.org/access/forms/frm0007.htm

It applies similarly to what I am trying to do, but it neglects to
show how to use the query with the function.

Any help would be appreciated.
Chas

 

[tina]

well, if you want to use a query that is saved as an object in the database,
then you can change the SQL statement in code at runtime. in query design
view, remove the criteria. in VBA, try the following, as

Dim str As String

str = CurrentDb.QueryDefs("QueryName").SQL

CurrentDb.QueryDefs("QueryName").SQL = str _
& "WHERE FieldName In(" & myFunction() & ")"

the above code will actually change the SQL statement of the query object.
note: make sure that your function returns a string where the values are
surrounded by *single* quotes, not double quotes.
also note: if your query includes any ORDER BY or GROUP BY clauses, you'll
need to revise the code to split the SQL statement into two parts and
concatenate the WHERE clause *before* the other clauses.

hth

 

[ChasW]

well, if you want to use a query that is saved as an object in the database,
then you can change the SQL statement in code at runtime. in query design
view, remove the criteria. in VBA, try the following, as

Dim str As String

str = CurrentDb.QueryDefs("QueryName").SQL

CurrentDb.QueryDefs("QueryName").SQL = str _
& "WHERE FieldName In(" & myFunction() & ")"

the above code will actually change the SQL statement of the query object.
note: make sure that your function returns a string where the values are
surrounded by *single* quotes, not double quotes.
also note: if your query includes any ORDER BY or GROUP BY clauses, you'll
need to revise the code to split the SQL statement into two parts and
concatenate the WHERE clause *before* the other clauses.

hth

Hi Tina,
I pretty much tried this verbatim and I am getting an error:
Characters found after end of SQL statement.

Which makes sense from the way I am understanding what you posted. If
the original statement ends with a ; then how would the addition of
the WHERE clause not be expected to yield this error.

I did clear all the criteria in design view.
In fact, the base query is just this:

SELECT *
FROM tbl;

Still confused somewhat,
Chas

 

[tina]

then just remove the semicolon from the SQL string before concatenating the
WHERE clause. something like

str = Replace(CurrentDb.QueryDefs("QueryName").SQL, ";", "")

or perhaps

str = CurrentDb.QueryDefs("QueryName").SQL
str = Left(str, Len(str) - 3)

if you use this second solution, add a space at the beginning of the WHERE
clause string, as

" WHERE..."

hth

 

[ChasW]

then just remove the semicolon from the SQL string before concatenating the
WHERE clause. something like

str = Replace(CurrentDb.QueryDefs("QueryName").SQL, ";", "")

or perhaps

str = CurrentDb.QueryDefs("QueryName").SQL
str = Left(str, Len(str) - 3)

if you use this second solution, add a space at the beginning of the WHERE
clause string, as

" WHERE..."

hth

This did help.
Thank you again!

Chas

 

[tina]

you're welcome

This did help.
Thank you again!

Chas

 

[Michel Walsh]

Hi,

If you have many (tons) of values, add then in a table, under a field, no
dup, and use an inner join:

SELECT a.*
FROM a INNER JOIN tempTable As b
ON a.field = b.field



If values are captured from a prompt, and are few in number, you can try:

SELECT *
FROM a
WHERE ("," & field & "," ) LIKE "*," & "enter your values delimited by
coma, no space: " & ",*"


where I assume the values entered are delimited by a coma, such as:

4,5,15

(note there is no space, as would it in 4, 5, 15 )



Hoping it may help,
Vanderghast, Access MVP