Using a VBA Function in the criteria of a query

[rirus via AccessMonster.com]

Is there a way to use a User Function (VBA) which pulls a process name from a
table of processes that can be used in the Criteria of a Query?

When the query is executed the User Function (VBA) pulls a process name to
identify the criteria for a process name and the query retrieves all the data
for that process in its results.

What I normally do is open a hidden form, enter in the process name into a
field and then run the query. The Criteria for the Process Name is then
pulled from that field and the query retrieves all the data for that process
in its results.

Thanks,

rirus

 

[Bob Barrows]

Is there a way to use a User Function (VBA) which pulls a process
name from a table of processes that can be used in the Criteria of a
Query?

Sure* Why not?
But why is a function needed for this?

*Unless you are using ADO to execute the query, or attempting to run it
from outside Access ...

 

[Bob Barrows]

Is there a way to use a User Function (VBA) which pulls a process
name from a table of processes that can be used in the Criteria of a
Query?

When the query is executed the User Function (VBA) pulls a process
name to identify the criteria for a process name and the query
retrieves all the data for that process in its results.

What I normally do is open a hidden form, enter in the process name
into a field and then run the query. The Criteria for the Process
Name is then pulled from that field and the query retrieves all the
data for that process in its results.

Oh wait, I think I misunderstood. I thought you were talking about
something like this:

.... where fieldname = getcurrentprocess()

After rereading your question I'm thinking you want something like:

.... where getcriterionforcurrentprocess()

Where the function would return something like "somefield = something"

No, that is not possible. You will need to have your function build the
entire sql statement dynamically and execute it, returning the results
as a recordset.

 

[rirus via AccessMonster.com]

Thanks Bob, that is what I was think as everything I tried and have seen is
saying it can't be done. The SQL statement in the function should do the
trick, thanks again.

rirus

Is there a way to use a User Function (VBA) which pulls a process
name from a table of processes that can be used in the Criteria of a

[quoted text clipped - 8 lines]

Name is then pulled from that field and the query retrieves all the
data for that process in its results.

Oh wait, I think I misunderstood. I thought you were talking about
something like this:

... where fieldname = getcurrentprocess()

After rereading your question I'm thinking you want something like:

... where getcriterionforcurrentprocess()

Where the function would return something like "somefield = something"

No, that is not possible. You will need to have your function build the
entire sql statement dynamically and execute it, returning the results
as a recordset.

 

[Bob Barrows]

Thanks Bob, that is what I was think as everything I tried and have
seen is saying it can't be done. The SQL statement in the function
should do the trick, thanks again.

rirus

Is there a way to use a User Function (VBA) which pulls a process
name from a table of processes that can be used in the Criteria of a

[quoted text clipped - 8 lines]

Name is then pulled from that field and the query retrieves all the
data for that process in its results.

Oh wait, I think I misunderstood. I thought you were talking about
something like this:

... where fieldname = getcurrentprocess()

After rereading your question I'm thinking you want something like:

... where getcriterionforcurrentprocess()

Where the function would return something like "somefield =
something"

No, that is not possible. You will need to have your function build
the entire sql statement dynamically and execute it, returning the
results as a recordset.

Alternatively, you could create a Sub to build the sql statement, and
then assign the string to the SQL property of a querydef.

 

[Klatuu]

Actually, it is sort of possible. If you have the function return a Boolean
value into a Calculated field and filter the field on True or False
(whichever is correct in the situation) it will do that.

 

[Bob Barrows]

Let's be clear: he seems to have a table with two columns: process and
criterion containing data like this:
proc1 | thisfield=thisvalue
proc2 | thatfield=thatvalue

He has a function that looks up the criterion based on whatever the
"current" process is. when the function runs it will return a string
containing either "thisfield=thisvalue" or "proc2 | thatfield=thatvalue"
depending on what the current process is. He is asking if he can create
a saved query whose sql lookls like this:

select ... from mytable where get_criterion_for_current_process() so
that when the query runs, the function retrieves the value and the sql
that results when the current process is "proc1" is:
select ... from mytable where thisfield=thisvalue


I don't believe this is possible. Do you really have a way to do that?

I think I see what you're suggesting, but I don't believe it is
relevant. OP, can you confirm my understanding of the problem?