Solved

Using a global variable to filter a query

Posted on 2001-07-24
11
218 Views
Last Modified: 2012-06-27
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?
0
Comment
Question by:hotathens
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +3
11 Comments
 
LVL 6

Expert Comment

by:cjswimmer
ID: 6313129
Have you tried referencing the form directly?

SELECT * FROM tblYourTable WHERE ID = [Forms]![YourFormName]![txtIDSelection]
0
 
LVL 2

Accepted Solution

by:
hotbudare earned 100 total points
ID: 6313165
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

    MyGlobalVarValue=MyGlobalVar
end function

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

HTH/EQTA
T.S.U. Mario Osorio
Punto Fijo, Falcon, Venezuela

0
 
LVL 6

Expert Comment

by:cjswimmer
ID: 6313170
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()
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 8

Expert Comment

by:dovholuk
ID: 6313184
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.

dovholuk
0
 
LVL 6

Expert Comment

by:Mach1pro
ID: 6313189
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
0
 
LVL 2

Expert Comment

by:hotbudare
ID: 6314199
dovholuk,
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.

HTH/EQTA
T.S.U. Mario Osorio
Punto Fijo, Falcon, Venezuela

0
 
LVL 11

Expert Comment

by:joekendall
ID: 6314274
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.

HTH,

Joe
0
 

Author Comment

by:hotathens
ID: 6316982
Joe

where would i put the code on the  form ?
0
 
LVL 11

Expert Comment

by:joekendall
ID: 6317417
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.

HTH,

Joe
0
 
LVL 6

Expert Comment

by:cjswimmer
ID: 6365652
any update hotathens?
0
 
LVL 2

Expert Comment

by:hotbudare
ID: 6377373
Thanks a lot hotathens ... but why the "B" grade?
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question