Using a global variable to filter a query

I set a global variable up and it?s value is set using data from a form. I then wish  to filter records in a query using this variable. I have tried putting [var name] on the criteria line but this does not work. Has anyone a suggestions?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Have you tried referencing the form directly?

SELECT * FROM tblYourTable WHERE ID = [Forms]![YourFormName]![txtIDSelection]
You'll have to create UDF that returns the variable's value, something like (in a standard module):

public MyGlobalVar as YourDataType

public function MyGlobalVarValue() as YourDataType

end function

and then use this function in your query.
Please keep in mind that this code has to be in a standard module ...

T.S.U. Mario Osorio
Punto Fijo, Falcon, Venezuela

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
you could also make a public function in a module that returns the variable and use the function in the criteria:

Public Function GetCurrentID() As Long
    GetCurrentID = GlobalID
End Function

SELECT * FROM tblYourTable WHERE ID = GetCurrentID()
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

cjswimmer's answer will suit you best. you don't need to use a function or a global variable if you reference the data from your form directly.

create a Public function and set its value to your global variable then reference the Function in your query.

Public Function MyVariable() As Long
     MyVariable = GlobalVariableName
End Function

In the criteria section of your query field just put in   MyVariable()
If your global variable is of numeric type and it hasn't been initialized, MyVariable() will equal zero.
If your global variable is not numeric and has not been initialized, you will probably get an error
You are right, but there's an advantage to my approach, and it is that in my proposed way hotathens will be able to use this query anytime, he'll just have to make sure he's got the appropiate values in the global variable(s)before he uses his query.

BTW, cjswimmer (in his second proposition) and Mach1pro are both using the same approach I originaly stated.

T.S.U. Mario Osorio
Punto Fijo, Falcon, Venezuela

If you will be using the query for a control on a form or for a report, you could write code to do it. For example:

Dim sSQL As String

'UserName is the Global Variable
sSQL = "SELECT Report, User " & _
       "FROM Table2 " & _
       "WHERE (User Like 'all' Or " & _
       "User Like '*" & UserName & "*');"

'Set the controls RowSource to the Sequel        
cboReport.RowSource = sSQL

'Or you can use everything above but change the
'cboReport.RowSource line to this
Report.RecordSource = sSQL

This is just another way to do what has been stated above.


hotathensAuthor Commented:

where would i put the code on the  form ?
You can put the code in the AfterUpdate event in the control that is used as the criteria. Then the control that needs this as a RowSource will automatically be updated with this criteria from your Global Variable.


any update hotathens?
Thanks a lot hotathens ... but why the "B" grade?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.