using a function to build the WHERE clause of a query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ChasW - 08 Apr 2006 14:58 GMT

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

Reply to this Message

tina - 08 Apr 2006 16:35 GMT

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

> 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.
[quoted text clipped - 29 lines]
> Any help would be appreciated.
> Chas

Reply to this Message

ChasW - 08 Apr 2006 17:21 GMT

>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
[quoted text clipped - 15 lines]
>
>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

Reply to this Message

tina - 08 Apr 2006 18:09 GMT

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

> >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
[quoted text clipped - 32 lines]
> Still confused somewhat,
> Chas

Reply to this Message

ChasW - 09 Apr 2006 11:59 GMT

>then just remove the semicolon from the SQL string before concatenating the
>WHERE clause. something like
[quoted text clipped - 12 lines]
>
>hth

This did help.
Thank you again!

Chas

Reply to this Message

tina - 09 Apr 2006 16:58 GMT

you're welcome :)

> >then just remove the semicolon from the SQL string before concatenating the
> >WHERE clause. something like
[quoted text clipped - 17 lines]
>
> Chas

Reply to this Message

Michel Walsh - 11 Apr 2006 12:42 GMT

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

>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.
[quoted text clipped - 29 lines]
> Any help would be appreciated.
> Chas

Reply to this Message