Global Variable as query parameter

[Micosoft]

Is it possible to use a global variable as a criteria in a query

Example:

I have a table:

year stuff
---------------------
2002 things
2003 more things
2003 additional stuff
2004 some stuff
2005 nothing yet
2004 last stuff

I have a query which has "2004" as the criteria for the year col, I want be
able to change the criteria programatically using a global variable defined
in a module.

But when I try to run the query by putting the variable name in, it keeps
asking me to type in a value.

Any help would be much appreciated.
Brian

 

[Ken Snell [MVP]]

Not directly. But you can use it indirectly by using your own function to
return the value of the global variable.

Put this function in a regular module:

Public Function GetVariable()
GetVariable = NameOfGlobalVariable
End Function

Then use the function in your query's criterion expression instead of the
name of the global variable.

 

['69 Camaro]

Hi, Brian.

Since "Year" is a reserved word in Access, I'd advise against using it as a
column identifier. You'll prevent unexpected behaviors later if you choose
a different identifier for your column name.

You can use a variable as criteria in a query, but you'll need to do some
prep work first. And this variable doesn't even need to be global. A
module-level variable is just fine.

In a standard module, define your module-level variable, then define a
public function that sets the value of the variable, then define a public
function that retrieves the value of this variable. Next, create your query
that uses this "get" function as the criteria for selecting records.

When you need to run this query, first call the "set" function in the
standard module to assign a value to the variable, then run the query. You
won't be prompted to enter a value when the query runs.

Example code in a standard module (need to add error handling to functions):

Option Compare Database
Option Explicit

Private m_nAcctYear As Integer

Public Function setAcctYear(nYear As Integer)
m_nAcctYear = nYear
End Function

Public Function getAcctYear( ) As Integer
getAcctYear = m_nAcctYear
End Function

------------------------------

Example SQL statement:

SELECT DeptSales.Stuff
FROM DeptSales
WHERE (DeptSales.AcctYear = getAcctYear( ));

------------------------------

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Any human can read my reply E-mail address and should alter it so that
a message will be forwarded to me. Spammers who use my UNALTERED
reply E-mail address will only satisfy the SpamEater's hunger.)

 

[Jeff Boyce]

Brian

If the other responses you received are not quite what you are looking for,
consider posting the reason why you want to do this -- you've described a
very specific "how" ("use a global var...."). Put another way, as a result
of being able to do the very specific thing you've asked, what will you have
accomplished that adds value to what you're working on?

 

[Dale Fye]

Another method, which avoids the global variable all together is to add a
control (txtYear) to your Splash screen. When you move off the splash
screen to your basic application, leave it open, but invisible. Make the
new control invisible as well, and store the value in that instead. Then in
your query, refer to the control on your splash form, something like:

SELECT * FROM yourTable WHERE [someField] = Forms("frm_Splash").txtYear

The advantage of this is that when Access generates an error, global
variable values can be lost, but if you store the value in a control on a
form, you can get at it as long as that form is open.

HTH
Dale

 

[Brian]

thanks guys,

I think this will do the trick for me

Brian