Passing input in brackets [] to a variable?

[mcl]

I use [describe input I need] in queries to ask for input. Is there anyway
to pass the input to a variable so I can use that input in other queries or
even in multiple queries in a macro?

 

[tina]

you can't use variables in queries. you can execute SQL strings withing VBA
procedures, and use variables there. you might find it easiest to simply
create a form with one or more controls to allow your user(s) to enter the
value(s), and refer to the form control(s) in your query criteria, as

Forms!FormName!ControlName

hth

 

[Steve Schapel]

By the way, Tina, just for interest at this stage, I suppose, but with
Access 2007 there is a SetTempVar macro action, which will give macros
much greater power and flexibility as regards variable management. You
can even subsequently use a macro-generated variable within a VBA procedure.

--
Steve Schapel, Microsoft Access MVP

you can't use variables in queries. you can execute SQL strings withing VBA
procedures, and use variables there. you might find it easiest to simply
create a form with one or more controls to allow your user(s) to enter the
value(s), and refer to the form control(s) in your query criteria, as

Forms!FormName!ControlName

hth

I use [describe input I need] in queries to ask for input. Is there anyway
to pass the input to a variable so I can use that input in other queries or
even in multiple queries in a macro?

 

[tina]

that sounds pretty cool, Steve. do you know if there will be a way to use
the macro variable in a query object - now that would be really handy!

By the way, Tina, just for interest at this stage, I suppose, but with
Access 2007 there is a SetTempVar macro action, which will give macros
much greater power and flexibility as regards variable management. You
can even subsequently use a macro-generated variable within a VBA procedure.

--
Steve Schapel, Microsoft Access MVP

you can't use variables in queries. you can execute SQL strings withing VBA
procedures, and use variables there. you might find it easiest to simply
create a form with one or more controls to allow your user(s) to enter the
value(s), and refer to the form control(s) in your query criteria, as

Forms!FormName!ControlName

hth

I use [describe input I need] in queries to ask for input. Is there anyway
to pass the input to a variable so I can use that input in other

queries
or

even in multiple queries in a macro?

 

[Steve Schapel]

Yes, Tina. There are some possibilities here. For example, you could
set a variable in a macro, e.g. based on a form control, or InputBox(),
and then have a UDF set up to return the value of the variable, so the
function could be used in the criteria of other queries. Another
possiblility is using VBA code to modify the SQL property of a saved
query, in order to reference the value of a macro-generated variable.

 

[tina]

well, yes, those options do require knowledge of VBA, though they're simple
enough to learn how to do. i was hoping for *direct* use of a
macro-generated variable in queries, bypassing the need for newbies to use
any VBA at all. oh well, maybe in the next version...

 

[mcl]

That's the problem. I don't know VBA.

well, yes, those options do require knowledge of VBA, though they're
simple
enough to learn how to do. i was hoping for *direct* use of a
macro-generated variable in queries, bypassing the need for newbies to use
any VBA at all. oh well, maybe in the next version...

 

[Steve Schapel]

Mcl,

Tina's suggested approach, of using unbound controls on a form for the
entry of the query criteria, is the best way.

In 12 years as a professional Access developer, I have not once found a
use for a Parameter Query (i.e. [describe input I need]).

 

[tina]

In 12 years as a professional Access developer, I have not once found a

use for a Parameter Query (i.e. [describe input I need]).

i agree with Steve, mcl. i personally don't like parameter queries because
you have no control over the data entry - if the user enters the data wrong,
the query bombs or returns "bad" data.

by using a control in a form as the criteria reference, you have the option
of setting a validation rule on the control, or checking the control's value
before running the query, with either a macro or VBA code. it's more
time-consuming for the programmer to set up, but in the long run it supports
the user better, which is the bottom line for me.

hth

Mcl,

Tina's suggested approach, of using unbound controls on a form for the
entry of the query criteria, is the best way.

In 12 years as a professional Access developer, I have not once found a
use for a Parameter Query (i.e. [describe input I need]).

--
Steve Schapel, Microsoft Access MVP

That's the problem. I don't know VBA.