Using a variable to define a query

[PAC]

I am still trying to fully understand the power of queries.

I know how to set a query so that it uses a value from a
form (ie. text box on a form) to filter data in the query.
What I cannot figure out is how to use a variable that is
set dynamically as the user moves from form to form.

For instance, lets say I define a variable (intABC) that
is defined in the GENDEFS module. Instead of using a text
box to set the value to be used in the query, I would like
to use my variable (intABC) instead. If I try using
[intABC] in the field in the query, the query then prompts
for the appropriate value.

How can I get my query to use my variable instead?

Thanks in advance!
PC

 

[Van T. Dinh]

Since the JET engine doesn't know about VBA hence it doesn't know about your
Variable so you cannot use the Variable directly.

However, JET recognises custom functions in Access so you can write a
wrapper function simply returning the value of the variable. Something
like:

Public Function Wrapper() As Variant '(or whatever type for the Variable)
Wrapper = intABC
End Function

in a Standard Module and then in the Query, you can simply use the function
instead of the Variable like:

.... WHERE [YourField] = Wrapper()

 

[John Spencer (MVP)]

If you have a global variable that you are setting and want to use it in a query
then you need to write a function to get the value of the variable and return it
to the query.

For example.

Public Function getIntABC() as Integer
getIntABC = intABC
End Function

Sample Query:
SELECT FieldA, FieldB
FROM SomeTable
WHERE FieldB = getIntABC()

 

[PAC]

Ok, I can see how this will work. Now, if I may expand on
this concept a bit, what if I need to have more than one
var in the statement? Like I might need 2 or 3 vars pulled
together in an AND clause. Like "X AND Y AND Z"? I
actually have 3 vars that I would like to filter the data
on so I would like to plugin 3 vars into the query.

And (if I may) another question...looking to efficiency,
is this an effective way of doing this in the query? I am
trying to learn to do somewhat "good" practice and coding
techniques so if I am learning a method to doing
something, I'll want to use this technique in other areas
as well later.

Thanks for the help!

PC

-----Original Message-----
Dear PAC:

Typically this is done by writing a public function that simply returns the
value in the variable. The query can access a function but not a variable.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

I am still trying to fully understand the power of queries.

I know how to set a query so that it uses a value from a
form (ie. text box on a form) to filter data in the query.
What I cannot figure out is how to use a variable that is
set dynamically as the user moves from form to form.

For instance, lets say I define a variable (intABC) that
is defined in the GENDEFS module. Instead of using a text
box to set the value to be used in the query, I would like
to use my variable (intABC) instead. If I try using
[intABC] in the field in the query, the query then prompts
for the appropriate value.

How can I get my query to use my variable instead?

Thanks in advance!
PC

.

 

[PAC]

Ok, I can see how this will work. Now, if I may expand on
this concept a bit, what if I need to have more than one
var in the statement? Like I might need 2 or 3 vars pulled
together in an AND clause. Like "X AND Y AND Z"? I
actually have 3 vars that I would like to filter the data
on so I would like to plugin 3 vars into the query.

And (if I may) another question...looking to efficiency,
is this an effective way of doing this in the query? I am
trying to learn to do somewhat "good" practice and coding
techniques so if I am learning a method to doing
something, I'll want to use this technique in other areas
as well later.

Thanks for the help!

PC

-----Original Message-----
If you have a global variable that you are setting and want to use it in a query
then you need to write a function to get the value of the variable and return it
to the query.

For example.

Public Function getIntABC() as Integer
getIntABC = intABC
End Function

Sample Query:
SELECT FieldA, FieldB
FROM SomeTable
WHERE FieldB = getIntABC()

I am still trying to fully understand the power of queries.

I know how to set a query so that it uses a value from a
form (ie. text box on a form) to filter data in the query.
What I cannot figure out is how to use a variable that is
set dynamically as the user moves from form to form.

For instance, lets say I define a variable (intABC) that
is defined in the GENDEFS module. Instead of using a text
box to set the value to be used in the query, I would like
to use my variable (intABC) instead. If I try using
[intABC] in the field in the query, the query then prompts
for the appropriate value.

How can I get my query to use my variable instead?

Thanks in advance!
PC

.

 

[Tom Ellison]

Dear PC (not PAC?):

One possibility is to pass the value from inside the query to a function,
and let the function see if the value is X, Y, or Z. But the most common
efficient way is to generate the SQL in code placing the values of X, Y, and
Z into the text of the query, possibly using:

WHERE SomeColumn IN (13, 7, 94)

where X = 13, Y = 7, and Z = 94.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Ok, I can see how this will work. Now, if I may expand on
this concept a bit, what if I need to have more than one
var in the statement? Like I might need 2 or 3 vars pulled
together in an AND clause. Like "X AND Y AND Z"? I
actually have 3 vars that I would like to filter the data
on so I would like to plugin 3 vars into the query.

And (if I may) another question...looking to efficiency,
is this an effective way of doing this in the query? I am
trying to learn to do somewhat "good" practice and coding
techniques so if I am learning a method to doing
something, I'll want to use this technique in other areas
as well later.

Thanks for the help!

PC

-----Original Message-----
Dear PAC:

Typically this is done by writing a public function that simply returns the
value in the variable. The query can access a function but not a variable.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

I am still trying to fully understand the power of queries.

I know how to set a query so that it uses a value from a
form (ie. text box on a form) to filter data in the query.
What I cannot figure out is how to use a variable that is
set dynamically as the user moves from form to form.

For instance, lets say I define a variable (intABC) that
is defined in the GENDEFS module. Instead of using a text
box to set the value to be used in the query, I would like
to use my variable (intABC) instead. If I try using
[intABC] in the field in the query, the query then prompts
for the appropriate value.

How can I get my query to use my variable instead?

Thanks in advance!
PC

.