Passing input in brackets [] to a variable?

  • Thread starter Thread starter mcl
  • Start date Start date
M

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?
 
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
 
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


mcl said:
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?
Click to expand...
Click to expand...
 
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! :)


Steve Schapel said:
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


mcl said:
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
Click to expand...
queries
or
even in multiple queries in a macro?
Click to expand...
Click to expand...
Click to expand...
 
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.
 
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... ;)
 
That's the problem. I don't know VBA.

tina said:
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... ;)
Click to expand...
 
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]).
 
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]).
Click to expand...

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


Steve Schapel said:
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.
Click to expand...
Click to expand...
 
Back
Top