Global variable in query filter

[Nikos Yannacopoulos]

Is it possible to use a global variable, already assigned
a value in a module, in the criteria line of a field in a
select query? Alternatively, in SQL (not in VB code)?
How is it referenced?

I want to do this in a query that is used as the
recordsource for a form.

Thanks in advance,
Nikos

 

[Gary Walter]

Is it possible to use a global variable, already assigned
a value in a module, in the criteria line of a field in a
select query? Alternatively, in SQL (not in VB code)?
How is it referenced?

I want to do this in a query that is used as the
recordsource for a form.

Hi Nikos,

One typical method is to declare the global
in a module, then write "get" and "set" functions
in the module for your global var.

For a simple example (with no error checking):

Option Compare Database
Option Explicit
Public gFY As String

Public Function SetFY(pFY As String)
gFY = pFY
End Function

Public Function GetFY() As String
GetFY = gFY
End Function

In your query using above example,
your WHERE clause might look like:

SELECT ...
FROM ....
WHERE GetFY() = [FY];

Please respond back if I have misunderstood.

Good luck,

Gary Walter

 

[Nikos Yannacopoulos]

Gary,

Many thanks for your time!

I figured out a function would do it hours after I posted
the question (I'm in Athens, Greece, 10 hours ahead of US
west coast time - quite a headstart), yet it feels a lot
better to be assured it's the right way!

Thanks again,
Nikos

-----Original Message-----

Is it possible to use a global variable, already assigned
a value in a module, in the criteria line of a field in a
select query? Alternatively, in SQL (not in VB code)?
How is it referenced?

I want to do this in a query that is used as the
recordsource for a form.

Hi Nikos,

One typical method is to declare the global
in a module, then write "get" and "set" functions
in the module for your global var.

For a simple example (with no error checking):

Option Compare Database
Option Explicit
Public gFY As String

Public Function SetFY(pFY As String)
gFY = pFY
End Function

Public Function GetFY() As String
GetFY = gFY
End Function

In your query using above example,
your WHERE clause might look like:

SELECT ...
FROM ....
WHERE GetFY() = [FY];

Please respond back if I have misunderstood.

Good luck,

Gary Walter



.