Home  Fees/Services  Access 2010 Template  Access Tutorial  Access Download  Articles  Search  Forums  Blockchain Tech  Contact  Privacy  Links

Query Parameter Global Variables
Use Global Variables as SQL Parameters

Visual Basic Tutorials:
Access-Google Earth
Age Calculation
Change To Proper Case
Email via Gmail #1
Send Gmail Email #2
Inactivity Logout Code
Outlook Email
Read Email Access
Email Attachment
Send Outlook Email
Running Sum
Denormalize Records
Stock Quotes
Find Database Path
Detail-Master Update
Data Field Validation
Field Value New-Old
Access Version
Global Variable Parameter
Global Variables
Active Labels
Files List Box
Mail Merge
Quick Sort
Recordset Filters
Reference Form Field
Select Case
Access Transactions

Visual Basic Function Examples

Global Variables Parameters

Need help with Global Variables? Your time is too valuable - call us and we will solve the problem or there is no charge! $100 1/2 hour.

After reviewing our example: all about global variables you are ready to put this knowledge to good use using global variables to pass parameters to a query. As with nearly all tasks in Microsoft Access there are usually four or five different ways to a solution.  Some are better than others in certain situations, and some are easier than others. Using variables to pass parameters to queries is one of these multi-solution tasks.  Here are some of the standard solutions:

    Are you just starting out on your quest for a small business database? Need some advice or someone to bounce ideas off of? We are happy to chat with you to discuss options to your situation.

    Are you looking for an MS Access template database? We have many for sale in our Access Template and Examples Software section.

  • Use a criteria entry in the query grid to define a parameter that pops up as a question when the query runs.

  • Store parameters in a temporary table that is unlinked but defined to the query.

  • Reference the form field control that contains the parameter value.

  • Build the SQL code in visual basic and either run it as a docmd.runSQL or create a stored query with the SQL text.

The last, less commonly used method is use global variables as query parameters.

New! Download Access example of  Global Variables

This is perhaps the neatest method and also allows the query to be used for multiple parameter forms, unlike when you reference the parameter form field directly.  It also provides a consistent method throughout your application and a more centralized location for key information.

The setup is as follows:  Create a Module and place the following code into it.  Note that you'll need to call this procedure (Get_Global) once when the database opens just to setup the variables.  You might want to do this in you opening form.

Option Compare Database

Global GBL_Project_ID As Long
Global GBL_Start_Date As Date
Global GBL_End_Date As Date

Option Explicit

Public Function get_global(G_name as string) as Variant

' property of blueclaw-db.com

     Select Case G_name
            Case "Project_ID"
            Case "Start_Date"
            Case "End_Date"
    End Select
End Sub

In the parameter form that runs the report you save the value selected by the user to the appropriate variable.  This is easily done by adding the following line of code in the After Update event...


In the query there's no more trying to figure out the full path to your parameter field which might be buried several sub forms deep or might change during development up or down, requiring the query reference to change.  Simple refer to the Get_Global function in the criteria of your query - here's the SQL:

Select Project_ID, Project_Name from M_Projects
Where Project_ID=Get_Global('Project_ID');

This is our method of choice for all except the simplest of databases.

Is there a downside to using this method - probably.  We haven't done any testing but one would assume that if you have 100's of case statements the lookup of the variable may slow the query down a tad.

Try our downloadable Access database demonstration of using Global variables as query parameters.

Blue Claw Database Design Downloadable Tutorial:
Choose Command Dynamic SQL Order By  

A Blue Claw Software Design Template:

Asbestos Survey Project Database

A Blue Claw Database Design Article:

How To Create Faster Access Databases

Microsoft Office:
MS Access 2000 Through 2016 and Office 365 & Sharepoint

Contact Information

Copyright 2000-2018 Blue Claw Database Design

Microsoft Access 2007, 2010, 2013 & 2016