How to Use Access Form Dates in a Pass-Through Query
Question details
The user needs to replace hard-coded dates in an Access pass-through query with dynamic start and end dates selected from form controls.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Building a dynamic pass-through query based on user input from an Access form.
- Observed behavior
- The query currently uses hard-coded dates, and the goal is to dynamically concatenate form values into the query's SQL property using VBA and DAO before execution.
Ensure you have a working pass-through query with hard-coded dates to use as a template, and back up your Access database before adding or modifying VBA code.
Update Pass-Through Query SQL Using VBA and Form Controls
Use a VBA script to capture the start and end dates from your form controls, concatenate them into a SQL string, and update the pass-through query's SQL property via DAO.
Because pass-through queries are executed directly on the backend server (like SQL Server), they cannot interpret Microsoft Access form references (e.g., Forms!MyForm!txtStartDate). To resolve this, you must construct the SQL statement in VBA, replacing the dynamic parameters with the actual selected values, and then overwrite the saved query's SQL property.
Open your Access form in Design View. Add two text boxes for the dates (name them 'txtStartDate' and 'txtEndDate') and a command button (name it 'btnRunQuery') to trigger the code.
Right-click the command button, select 'Build Event...', choose 'Code Builder', and click OK. This opens the VBA editor within the button's Click event.
In VBA, declare variables and use the Format() function to ensure the dates match your backend database requirements (e.g., strStart = Format(Me.txtStartDate, "yyyy-mm-dd")).
Create a string variable that holds your complete SQL syntax. Concatenate the formatted date variables into the string where the hard-coded dates used to be. For example: strSQL = "SELECT * FROM Sales WHERE SaleDate BETWEEN '" & strStart & "' AND '" & strEnd & "'".
Use DAO to assign the newly constructed SQL string to your existing pass-through query. Write: CurrentDb.QueryDefs("YourPassThroughQueryName").SQL = strSQL.
Add code to execute the query or open a report based on the newly updated query definition using DoCmd.OpenQuery "YourPassThroughQueryName".
Looking for a free alternative to Microsoft Office?
While Microsoft Access handles specialized database management, if you need a free, lightweight, and highly compatible suite for your documents, spreadsheets, and presentations, WPS Office is an excellent choice. It offers a familiar interface and seamless migration for your daily office tasks without the hefty subscription fees.
- 1. Download the Installer: Visit the official WPS Office website and download the free installation package.
- 2. Install WPS Office: Run the setup file and follow the on-screen instructions for a quick, lightweight installation.
- 3. Open Your Office Files: Launch Writer, Spreadsheets, or Presentation to seamlessly open, edit, and save your existing Office documents.
Frequently Asked Questions
Why do I need to use VBA to change dates in an Access pass-through query?
Unlike standard local Access queries, pass-through queries send SQL commands directly to the backend database server (like SQL Server or Oracle). The backend server cannot read Access form references like [Forms]![MyForm]![txtDate], so you must use VBA to inject the literal date values into the SQL text string before executing it.
How should I format the date string in my VBA code for a pass-through query?
The date format depends entirely on the requirements of your backend SQL database. For Microsoft SQL Server, it is usually safest to use the ISO format (YYYYMMDD or YYYY-MM-DD). You can enforce this in VBA using the Format function, such as Format(Me.txtStartDate, "yyyy-mm-dd").
Can I pass parameters directly to a pass-through query without modifying the SQL property?
No, traditional pass-through queries in Access do not support parameter binding natively. The most reliable and widely used method is using DAO in VBA to completely rewrite the QueryDef.SQL property with the concatenated dynamic values prior to running the query.
