Create Dynamic Queries Using The Query Builder and Function Calls
The Access Query builder is fast and easy to use. Constructing SQL statements in VBA, however, requires additional attention to syntax and is not as user friendly when reviewing someone else's code.
The purpose of this article is to illustrate how to use the standard query builder and be able to select dynamic information from forms or VBA global variables and use those values to filter a query --- eliminating the need to write complex native SQL statements.
The key to developing dynamic filtering is the special way to code the "Criteria" section of the query builder. Instead of inserting hard coded constants for filtering, function calls can be used to return values from global variables. The developer can also reference form objects for the criteria, but global variables are somewhat easier and don't require a form to be open to successfully deliver values.
The following example shows how to filter a date range selected by a user from two calendar controls: a report start date and a report end date. At a high level, the steps are as follows:
(1) Create a public module that defines and initializes the start and end date global variables
(2) Run this public module at DB startup time using the AUTOEXEC macro
(3) Present the user with the calendar controls to select the date range
(4) Save the dates selected by the Calendar Control to the global variables defined in step (1)
(5) Create two public functions: one to reteurn the start date and one to return the end date
(6) Code a standard query using the query builder and in the criteria section, reference the functions to filter the date (as illustrated later in this document)
Program Code For Step 1 - Define and Initialize the Global Date Variables
' ******************************************************* ' This Module is Called by the AutoExec Macro at Startup ' It Defines and Initializes the Global Date Variables ' ******************************************************* Option Compare Database Option Explicit Public dteGlblReportDateStart As Date Public dteGlblReportDateEnd As Date Public Function InitializeReportDateGlobals() ' *********************************************************************** ' Initialize The Dates at Access DB Startup ' *********************************************************************** dteGlblReportDateStart = #1/1/2050# dteGlblReportDateEnd = #1/1/2000# End Function
Create an AutoExec Macro To Initialize Variables
Execute The Initialization Code From AutoExec
Present The User With A Calendar Control To Select The Report Date Range
Name The Calendar Controls in the Properties Area
The two calendar controls are used to update the global area dates when the user selects the dates
Name the Calendar Control for the report start date as "BeginReport"
Name the Calendar Control for the report end date as "EndReport"
Code the Calendar Control click event as shown below:
Calendar Control Event Code
Private Sub BeginReport_Click() dteGlblReportDateStart = BeginReport.Value End Sub Private Sub EnbReport_Click() dteGlblReportDateEnd = EndReport.Value End Sub
Code The Functions Used To Return Global Date Values
These public functions (in the regular modules section) are referenced from inside the "Criteria" section of the query. They return the values which were inserted into the global variables by the Calendar Controls.Public Function GetReportDateStart() ' *********************************************************************** ' Get The Report Start Date ' *********************************************************************** GetReportDateStart = dteGlblReportDateStart End Function Public Function GetReportDateEnd() ' *********************************************************************** ' Get The Report End Date ' *********************************************************************** GetReportDateEnd = dteGlblReportDateEnd End Function
Write the Query and Reference The Date Functions
