Access Vba Run Query With Parameters


It allows you to run access tasks without actually opening the database. Does anyone know if there's a way to query a stored procedure with dynamic parameters in Access 2013, without VBA?. I want to call a VBA sub from Powershell. Whenever you want a query to ask for input each time you run the query, you create what's called a parameter query in Access 2013. Code: Finding The Topics You Started or Replied To Quicker:. how Access and SQL Server can work together. If you want to import part of a query it might be worthwhile looking into import a Parameter query to Excel which is covered in a related topic. MS Access 2003: Create a query that accepts Start date and End date as parameters. For instance, let's say we own a bakery and want to create a query. Different ways to open an access report using DoCmd. execute, but says it cant execute a SELECT query. In Excel 2016, Power Query is no longer an Excel add-in but a native feature of Excel, and what’s more, you can now use VBA to create and manage Power Query queries. How to Use MS Access Forms to Pass Values to Queries. Use the VBA DAO Database Execute command, rather than DoCmd. Excel VBA offers the programmer access to web query parameters through the Parameters collection of Parameter objects. On my computer, this application is working like a charm. Home > Tutorials > Access Example. This method suppresses the typical Access warning messages, such as "You're about to run a query that will " and the ones you get if there are any records that fail, along with a vague reason for the failure. You can use either an existing query or a filter that was saved as a query. Running a query with a macro is very simple. parameter to it in VBA I am using the following code: Dim stDocName as String Dim db as Database Dim qdf as QueryDef Set db = CurrentDb Set qdf = db. Here's a simple subroutine that exports an Access table to Excel using VBA code. The FreeVBCode site provides free Visual Basic code, examples, snippets, and articles on a variety of other topics as well. Learn how to setup and program a multi select parameter list for supplying parameters to a Microsoft Access query. This is so that the queries run against the sql server do not hang up the server for too long. I want to be able to add or change the table name. Hi, i have build a query that i would run when a user press a button on a form. Code - FAQ: Announcement Dream. Where to enter parameter in below VBA code or ADO connection. To change query timeout value in Registry, follow these steps: Open Registry Editor. Question: In Microsoft Access 2003/XP/2000/97, I'm trying to run a query based on a date. Access's CreateQueryDef statement is a visual basic function which will clean up that growing list of queries and allow you to use form field variables with an SQL Server database back end. I wanna execute a query with one parameter from MS VBA. Nevertheless it is possible to change the column headings and the formatting appearance of data in each column. I’ve found two sources of information about how to use VBA with Power Query in Excel 2016. Pass Parameter from Excel through MS Query to MS Access Query The parameter is a date field, and I have configured that in the Access query. · Hi, Please read this. step 1, step 4 and step 5) have parameters defined. In this example, you will learn VBA Select Case Statement and Database. Die Execute-Methode. how Access and SQL Server can work together. CommandText = "qryDeleteCompany". Action queries allow us to delete specified records, append records from one table to another, update information in a group of records, or create new tables. the trouble is that the parameter is a control in a form. Access Tables and Queries only contain basic presentation tools because it is assumed that data will be presented to users in Forms or Reports. What I tend to do is build the query I want to run in SQL Server Management Studio first. Access: VBA Execute Query. The Access VBA Docmd object allows you to run a macro command within a visual basic module. Access™, 2007 VBA Programming FOR DUMmIES‰, Microsoft access 2010 vba programmiby Học Huỳnh Bá 4422 views · Microsoft. If the query has parameters, the current values for the Command object's parameters are used unless you override these with parameter values passed with the Execute call. Opening a Query with VBA. At any time you are paused either in step-by-step mode or at a breakpoint (see below), you can press F5 or Continue from the Run menu to cause VBA to run to completion or until a pause statement is encountered. Existiert bereits eine Access Abfrage, die man ausführen möchte, dann reicht ein DoCmd. You have a query and want users to be able to type in multiple parameters without having to fill in loads of boxes. MS Access Topic Stats Last Post Info; Announcement New To Dream. To enable the database for the current session, follow these steps:. I suggest you start again and build a VBA procedure. Access 223: 1. The syntax for specifying input parameters is the same for both. But when I want to pass parameters to temporary variables and create table variables and edit them to get the desired result instead of doing 10 to 15 Joins and mentioning the parameters in the where clause I get errors [Micro. Free ms-access sample database downloads. You know that VBA will allow you to use optional parameters, but this requires you to know exactly how many items you might ever need to pass, and in your case, it's impossible to predict that value. Cell value as parameter for VBA access query. Sometimes that means breaking up strings of data into smaller pieces so that you can run queries and reports based on the information they represent. When you run a parameter query from the user interface, Access can find the parameters if they have already been satisfied using a form and run the query. MS Access 2003: Create a query that accepts Start date and End date as parameters. View 2 Replies. In a Microsoft Access workspace, if you provide a syntactically correct SQL statement and have the appropriate permissions, the Execute method won't fail — even if not a single row can be modified or. The numbers in the range being searched or the row source for the combo box are corrupt. Click Start -> Run and then type regedit in the command box. Once you've developed your skills in working with Access queries you may have created parameter queries. A Tutorial on Building SQL in VBA. See VBA FAQ 43. bat") In the next section, I'll explain the full steps to create a VBA to run a batch file. Access version 2010 I think you should be able to post it in tips and tutorials - it does seem like the kind of thing a lot of people would be interested in doing. The best solution in my opinion is a simple custom function in which you can specify whether or not to close the external database instance. This Access download will provide you with … Access Database Download: Access Security Alternative Access Example Download, Download Access Examples Free. Now, in cell B2, type in "Customer ID:", and in cell D2, type in "ALFKI" (both without the quotes). Use a Custom VBA Function. Click on the OK button to execute the parameter query using the parameters collected on the form. How do I utilize VBA instead of Access to modify the following data parameters? solved. Working With Web Query Parameters In VBA. A message box is a special dialog box used to display a piece of information to the user. and I still don't know what is the code I need. This procedure will make sure that the queries run in access against the data in sql server will not access data for more than two years back. 05:15 run the query again but we have a problem it's VBA Beginner - Opening a Query - Nifty Access Is on YouTube HERE:- https 2. Choose the type of music from the combo box and the range of years to include in the two text boxes. Opening a Query with VBA. QueryDefs(QUERYNAME) With qdf. You can even use it to add or delete tables, but that is an uncommon use (data-definition query). Create a recordset that will contain the query results. an Access Macro but I have chosen to use VBA. A query is a set of instructions that you can use for working with data. you can run parameter queries from access VBA, the query will still prompt u building a string and doing it in VBA, is a better solution for this requirement by al means you can still leave your query and have the option to run it manually. Crosstab Queries, Part 2 Create a Crosstab Query Column Heading Row Heading Value Adding Date Criteria Defined Query Parameters Breaking Down by Year Specify Column. Manipulate QueryDefs From VBA. OpenRecordset("SELECT * FROM Table1") Similarly, you can open a recordset based on a query, like so:. button, the parameters in. You can use the following VBA to run a batch file from MS Access: Shell("Path where your batch file is stored\file name. Query based on a parameter query, Using VBA how do I set the parameters MS-Access 2002 I have a query (Q2) based on a parameter query (Q1) and need to set the parameters within a VBA module. I have a VBA median function that takes the median of nonzero values in a query. Once you have completed this set of videos, you…. RunSQL is all well and good for simple code, the number of times I've run into problems with unescaped apostrophes and the like was starting to nark a bit. How to enter query parameters in VBA Access / Other/unknown / Office 2007 I would like to take the records from the dynaset above and run the report on this. Just use the OpenQuery command and specify the query name to run. openquery with docmd. Does anyone know if there's a way to query a stored procedure with dynamic parameters in Access 2013, without VBA?. Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA: Date Parameter in a Union Query User Name: one at a time as you run this, and then. SQLStatement is an SQL statement for an action query. The last thing I want to do is to send them right clicking and refreshing everywhere, so I program a button to refresh Power Query with VBA. The best solution in my opinion is a simple custom function in which you can specify whether or not to close the external database instance. This technique does not work as we must update a number of memo fields, and query parameters max out at 255 characters. i would like to set it up to limit the data returned to excel to records that correspond to values that are present in a range of cells. I need to do a query that looks at orders over a date range and want to have the query ask for the dates when it is run. Using an Unbound Form to Obtain Query Parameters In this post we are going to use an unbound form to obtain the parameters for a query. If you do not want to display the parameters, you can use the other EReR_ProcessOptions to directly print, print. What did you expect to make and did you? What can someone like myself expect to make. Controlling data from Microsoft Access into Excel is one of the cooler things available in Excel. I will need to create a macro to look at the following parameters:. MS Access Topic Stats Last Post Info; Announcement New To Dream. My sql query for the row source is:. How to embed SQL queries into Excel's cells, execute them, and return the results into another Excel cell How can I create dynamically updating SQL queries in Excel? A few years ago I had a need in a project I was working on to have some cells in Excel receive their data as the result of a direct query of the database. In this tutorial, you will learn how to how to open and run queries in VBA. For instance, you can run the macro named MyMacro like this:. Let’s suppose that you already have an Access database, and that the Access database contains a query called “max_sales” Here are the steps that you’ll need to follow in order to create a VBA to run the “max_sales” query: Step 1: Add an Access Form. It allows you to run access tasks without actually opening the database. Tutorials and How To: How To Pass Parameter Value in MS Access Query. You just employ the methods described here to specify the criteria. Browse other questions tagged sql vba ms-access-2010 or ask your own question. How to enter query parameters in VBA Access / Other/unknown / Office 2007 I would like to take the records from the dynaset above and run the report on this. Click on the OK button to execute the parameter query using the parameters collected on the form. One row represents a column; Scope of rows: - all columns in specified table. parameter to it in VBA I am using the following code: Dim stDocName as String Dim db as Database Dim qdf as QueryDef Set db = CurrentDb Set qdf = db. Attempting to run a script in VBA that will pull data from an access database using SQL and put it into an excel spreadsheet. If you want to work with the data in VBA then you would do a Select Query using a Recordset operation. There will be 4 buttons on the form and each one will send different parameters to the query to execute. [Microsoft][ODBC Microsoft Access Driver] Too few parameters. I know that you can write your own VBA script to execute a custom SQL command, but I've heard that the VBA scripts don't work when you publish the project to SharePoint. The next section of the code provides an example of reading data from your spreadsheet or dashboard into VBA to be used as dynamic parameters. This article recommends Execute in preference to RunSQL. Export A Table From Microsoft Access To An Excel Spreadsheet Using VBA A Simple Subroutine. Pass parameter to a query. parameters property to achieve this) Thanks, Ben. Public Sub DeleteCust(lngID As Long) Dim cmd As ADODB. Complicated query to traspose data and search records that approach a value AccessForums. Multiple Combo Boxes as Criteria for A Query. Play around with the web query options and properties by right-clicking within the query results and selecting Parameters or Data Range Properties or Edit Query. Database Solutions for Microsoft Access - Plan for Null Responses in Your Parameter Queries: Parameter queries in your database are a favourite with many users because they're efficient, easy to create, and they offer a dynamic solution when you want to query the same field for different criteria. The form and the query work together to provide the desired dataset. If you are like me you hate working on large databases with dozens or 100's of queries. Once the parameter has been supplied, the query can return the results, based on the parameter provided. My concrete goal is just to be able to run all 300 of these without doing them 1 by 1. We want to be able to run our queries by simply clicking a button on the dashboard. Click on the OK button to execute the parameter query using the parameters collected on the form. You can use SQL queries to access these tables. I need to write a stored procedure that sits between sql server and MS access. I really want Excel to take the value in a given cell and pass it to the Access query as parameter. " Here are the steps in detail that I was not able to supply in the forum reply. It's free to sign up and bid on jobs. Evaluating Query Parameters in VBA The problem arises that if you merely try to execute a query with a criteria to a form control, it will result in an error “ Too Few Parameters “. If we create and run this stored procedure as is it will not return any data, because it is looking for any City values that equal NULL. We will get Excel to read in the value of cell D2 and pass it to the Stored Procedure as the CustomerID parameter. This process does a lot of things behind the scene, such as updating records, deleting and recreating tables, or emptying tables. However, I want to connect to this query from within Excel as a data source. So in this example we use the NULL option to allow you to not pass in a parameter value. In this example we will demonstrate the use of an extended select list box as a method to pass parameters to an Access query. You can use SQL queries to access these tables. Sub Execute. i have design an access query which seem like this: SELECT Replace(names,'lion','kiss') AS Expr1. This article describes how to dynamically change a SQL Passthrough query and a local Access query. I am trying to create an 'Update Query' in vba but I am missing something. On the right see my Excel Worksheet and the Message Box with the similar output from my VBA Macro. A popular way to let end users search for information is to provide a form with many combo boxes on it which have drop down lists of data from a table or query, then to base a query on the values in those combo boxes. This allows you to use the same query over and over without having to constantly open it in Design view to edit the criteria. QueryDefs("qrySelect") qdf. Im using ADODB. There will be 4 buttons on the form and each one will send different parameters to the query to execute. using VBA to set criteria in a query and refresh a corresponding subform I want the command button to refresh my subform (which is run from a query, "qryBuildingSearch") based upon the text that is in the text boxes. Code: Finding The Topics You Started or Replied To Quicker:. First, there are some code samples on. step 1, step 4 and step 5) have parameters defined. This method suppresses the typical Access warning messages, such as "You're about to run a query that will " and the ones you get if there are any records that fail, along with a vague reason for the failure. An Excel Worksheet and the Output of the RunSELECT Macro Let see how to run a simple SELECT SQL Query in Excel VBA on an example Excel Worksheet. the Access application is an object, a query definition. How to execute a query in ms access db from Excel VBA code or macro. Note 2nd and 3rd queries cannot be done in Access except as Pass Through queries due to sum aggregation. You can choose a cell that will be used for the upper left cell of the imported table. The reason for this is I have to use dynamic query to query a linked server from a database built in Progress(Uhg!). The last thing I want to do is to send them right clicking and refreshing everywhere, so I program a button to refresh Power Query with VBA. Step 1: Open the Excel Workbook and got to VBA code builder (Alt + F11) and Open a New Module. This assumes (1) the Pass Through query has been saved in Access, and as MyAccessQuery, and (2) the query will execute a SQL stored-procedure with a single numeric parameter - a string parameter. Global Variables as Query Parameters Using global variables to pass user selection criteria to queries is a useful programming technique to master. CommandType = adCmdStoredProc. Recordsets have many methods and properties to make working with the records in the collection easy. When a parameterized query is run in Access, Access provides an expression service that evaluates many parameters. VBA Access (31). I am trying to run a query specific fields of table in Ms Access, through Excel VBA. Query Access database using ADODB and write results to spreadsheet Insert data from Access to specific location in a workbook VBA Express : Excel - Query Access database using ADODB and write results to spreadsheet. There are three basic steps that are necessary: Create the form with the combo box(es) that will supply the parameters for the query. QueryDefs are stored queries that reside in Access. Run a mySQL Query With a Parameter from. The following article aims to illustrate how you can go about creating, modifying, deleting and executing database queries, using Excel VBA. One should thus use Excel VBA to execute the queries, rather than contain the query code and logic. Is there a way to pull the dates with VBA and run the query without any user interaction. We will use VBA to construct the Where clause (to limit records in the query) and then modify the SQL statement that defines the query. A message box is a special dialog box used to display a piece of information to the user. On my computer, this application is working like a charm. If I just use SQL in Excel, the problem I have is that someone might amend the query in the database. A query is, in the Power Query language, a program. My sql query for the row source is:. Use multi-select list boxes to pass parameters to an Access query. Performance Notes: 1. For example I have a list of dates in Column A and I want to run the query for each dates shown in column B, so the paramter for B1 is A1, B2 is A2 etc. You will not use DoCmd. One method for doing that is to use the Split function in Visual Basic for Applications (VBA). when you run a parameter query simply asks the user for some text input. The maximum length of the SQLStatement argument is 32,768 characters (unlike the SQLStatement action argument in the Macro window, whose maximum length is 256 characters). When you're running parameter queries, search terms act as variable criteria, which are query criteria that change each time you run the query. save Save Access VBA Manual For Later. Click the Get Data button. execute, but says it cant execute a SELECT query. Evaluating Query Parameters in VBA The problem arises that if you merely try to execute a query with a criteria to a form control, it will result in an error “ Too Few Parameters “. Parameters["prmSalutationId"] = 1 DoCmd. Connection. Run a parameter query within an Access form. If you would open the query in VBA, you could supply the parameter. I recorded a macro of importing the query and then modified the query slightly in the VBA code. OpenQuery is a method used to open a query. If you create a parameter query in query design view, you enter criteria data under one or more field headings in the form of text surrounded by square brackets. Since MS Access limits a field or column name to 64 characters, I thought I would use VBA to populate the spreadsheet beginning at row 2 with the following. Choosing a Table to Query. Before you can create a query you have to navigate to the Query Tab in your Access database. The array can have multiple dimensions. When you create a recordset from VBA, however, the Jet engine isn't able to locate the parameter references. OpenQuery method to open/view a query that you have created. Modify Query Parameters Create Table We can see what is in this collection by looking at your queries within the Navigation pane of Access. BigQuery supports query parameters to help prevent SQL injection when queries are constructed using user input. I have created a parameter query using Microsoft query as mentioned here. The code builds query that returns no rows (where 1=0 condition), but it returns column names that are parsed in fornext loop. Text So whenever I run this query from VBA its ask me for the parameter. Import Access Parameter Query to Excel. You can choose a cell that will be used for the upper left cell of the imported table. ) This radically extends the power of JET, but it makes no sense to call VBA if SQL can do the job. Then, you can attach these queries to buttons or labels on a form, with a macro which opens queries. To do this, select Parameters under the Query menu. Now the MyString variable would contain the value "WEBCHEATSHEET". This query must read a txt control that contain a date value on the same form where the button is [SOLVED] Access 2013: how to pass a field date value to a query from a form. I have built the parameters into the queries but on running the macro I have to enter each of the dates 3 times - once for each query. Describes a problem that may occur when you try to run a query, a form, or a report in Access 2002, in Access 2003, and in Access 2007. In this example, you will learn VBA Select Case Statement and Database. There are multiple ways to run the query through macros or code. com ( SS64 ). Action queries allow us to delete specified records, append records from one table to another, update information in a group of records, or create new tables. Add the Parameter to the Criteria section, of the relevant field in Query Design View: When you run the query, the parameter will appear as: Simply type in the value you wish to see. From the Data menu choose Get External Data > Run Saved query. Parameter at run time. I tried docmd. Microsoft Access Help Database Forums and Wiki by UtterAccess. Excel VBA offers the programmer access to web query parameters through the Parameters collection of Parameter objects. The parameters are defined in the query. The Choose a Query dialog displays defaulting to the Queries folder. ConnectionString = "Provider=Microsoft. Home > Tutorials > Access Example. There are usually two kinds of message boxes you will create: one that simply displays information and one that expects the user to make a decision. Open "dbtest" Dim rsTemp As ADODB. Just use the OpenQuery command and specify the query name to run. View: Optional: AcView. If you are running an Action query, you can do the same thing; however, Action queries usually display warning messages prior to changing data. This is so that the queries run against the sql server do not hang up the server for too long. This MSAccess tutorial explains how to create a parameter query that performs a wildcard search in Access 2003 (with screenshots and step-by-step instructions). Although we can create External Data Sets in Excel that pull information from a SQL Server Database there are times when you want to check a value or lookup a value from an underlying SQL Server database directly from an Excel formula. Specifically I want to call a sub or function in a module in an MS Access database. SQL Update query in VBA for Access If this is your first visit, be sure to check out the FAQ by clicking the link above. To do so, specify the query name in the Database object's OpenRecordset method to run a select or action query: Set rst = dbs. These videos lead you through from creating a simple Command Button right through to manipulating an SQL Statement. Opening Comments about Action Queries. Sometimes when you open an Access object (such as a table, query, form, or report), Access displays the Enter Parameter Value dialog box. For instance, let's say we own a bakery and want to create a query. Steps to Create a VBA to Run a Query in MS Access. Here is the VBA list box default values in Excel. Queries: Use a parameter to return all records if Null. Hi PamelaDV, What I do is write that pass through query each time, just before I execute it. One should thus use Excel VBA to execute the queries, rather than contain the query code and logic. Once you've developed your skills in working with Access queries you may have created parameter queries. Access VBA run Query or Action Query Imagine you have to run 10 Action Query (e. The query then runs and the new data is returned to Excel. Click on the OK button. Multiple Query Parameters from a single Prompt. My sql query for the row source is:. The "Enter Parameter Value" dialog box may appear unexpectedly. In this case, since the whole point is to run the query from VBA, more variable type names would probably be appropriate. Using VBA we can do this!. One method for doing that is to use the Split function in Visual Basic for Applications (VBA). Query Parameters in VBA I have a client who wants an Excel report with very long (over 64 characters) column names. using VBA to set criteria in a query and refresh a corresponding subform I want the command button to refresh my subform (which is run from a query, "qryBuildingSearch") based upon the text that is in the text boxes. Use a Custom VBA Function. While DoCmd. Since MS Access limits a field or column name to 64 characters, I thought I would use VBA to populate the spreadsheet beginning at row 2 with the following. Because parameter queries are so simple, they can be easily updated to reflect a new search term. Sometimes that means breaking up strings of data into smaller pieces so that you can run queries and reports based on the information they represent. All the best ,Cody. Controlling data from Microsoft Access into Excel is one of the cooler things available in Excel. Once you've developed your skills in working with Access queries you may have created parameter queries. Access: VBA Execute Query. FilterName refers to a query in the current database. You can use the DoCmD. Executing a SQL Statement Containing Parameters : SQL Parameter « Access « VBA / Excel / Access / Word. Background Action queries in Microsoft Access are queries that add, modify, or delete records from tables using INSERT, UPDATE, and DELETE commands (or create a. Click on the OK button to execute the parameter query using the parameters collected on the form. Whenever you are paused in step-by-step mode, you can query or change a variable's value from the Immediate window. The method in this article uses Select Queries which are much faster than the method explained in that article. If you want to work with the data in VBA then you would do a Select Query using a Recordset operation. Instead of you having to anticipate the various combinations of criteria that you are going to need and creating a separate query for each, you use parameters to prompt for information when the query is run. I have built the parameters into the queries but on running the macro I have to enter each of the dates 3 times - once for each query. 1) begins by asking for the user's name and birth date. If the amount of data is large and running an Access query takes too long, consider alternatives such as marshaling the content of the table as XML document to the SQL Server. SQL Update query in VBA for Access If this is your first visit, be sure to check out the FAQ by clicking the link above. You run a query to perform these instructions. BigQuery supports query parameters to help prevent SQL injection when queries are constructed using user input. This example utilizes several advanced VBA functions to export an Access table to Excel. Crosstab Queries, Part 1 What is a Crosstab Query? Similar to an Excel PivotTable Format() Function "yy-mmm" Sales Totals by Month Sales Totals by State by Month 2. When you read the word query in the text below, understand it to be a prepared, saved and tested Querydef object in Microsoft Access. JET can also call Visual Basic for Applications code (VBA. You can also run a data-definition query. openquery with docmd. This is so that the queries run against the sql server do not hang up the server for too long. Using UserForm parameters in ADO SQL Queries January 31st, 2006, 19:44 It means more people are getting into using VBA & ADO 'run query to find records Dim. bat") In the next section, I'll explain the full steps to create a VBA to run a batch file. Please guide. execute, but says it cant execute a SELECT query. Parameter at run time. Keep the heavy data in Access and call upon parts of it as you need it with the assistance of a Parameter Query. Background Action queries in Microsoft Access are queries that add, modify, or delete records from tables using INSERT, UPDATE, and DELETE commands (or create a. First, run Excel, and create a blank workbook. Hi - I have 3 access queries which I run via 1 macro. · Hi, Please read this. Execute Method. How to Run a Query in MS Access 2007. From the Data menu choose Get External Data > Run Saved query. Problem: While running the query via VBA code "QryCreateTbl_Test", it prompts for cust Id. However, you will need to handle VBA errors, such as the one shown below. The "Enter Parameter Value" dialog box may appear unexpectedly. contact; f Append Query works in query view but not when run from VBA. The query can be defined through the parameter -query. I have built the parameters into the queries but on running the macro I have to enter each of the dates 3 times - once for each query. Re: Run an Access Query from Excel VBA Command objects are used to avoid sql injections, in instances where we need to pass parameters, and it could be Prepared to facilitate succeeding executions. Describes a problem that may occur when you try to run a query, a form, or a report in Access 2002, in Access 2003, and in Access 2007. There are multiple ways to run the query through macros or code. A great question come up on the Power Query forum: "I would like to know if there is a way to pass parameters to a Stored Procedure used within a Power Query. You have to build the SQL in VBA. When I use a macro to run the report, the parameter request pops up and the user can enter a date. The parameters will prompt you to enter the start and end dates, or other criteria that you want to have as variables. You know that VBA will allow you to use optional parameters, but this requires you to know exactly how many items you might ever need to pass, and in your case, it's impossible to predict that value. It has been quite some time since I have had this requirement and 2 years ago when I worked with this the only option available was to use VBA and and DAO (or ADO depending on who you ask, haha). This resolves two dilemmas: it allows reviewing the estimated execution plan, and it confirms that the query will run successfully.