• Ms Access Query Parameter Examples
• Date Parameters In Microsoft Query Wizard Download
• Access Parameter Query Between Dates

Three generations of the same task

by Dany Hoter, DataRails Solutions Architect

Task definition

The goal is to import data into Excel from an SQL table with some input parameters. The parameter values should come from cells in Excel, and the data should be refreshed automatically anytime the value of any parameter changes in the grid.

Oldest method: MS-Query

Work directly in Microsoft Query for other types of queries If you want to create a more complex query than the Query Wizard allows, you can work directly in Microsoft Query. You can use Microsoft Query to view and to change queries that you start creating in the Query Wizard, or you can create new queries without using the wizard. So, to select old records with a query, I can put a parameter in my original query, replacing the date. The Parameter is enclosed in square brackets. With the query open in Design view, on the Design tab, in the Show/Hide group, click Parameters. In the Query Parameters box, in the Parameter column, enter the prompt for each parameter you want to specify a data type for. Make sure that each parameter matches the prompt that you used in the Criteria row of the query design grid. A way around this is to create a generic Microsoft Query, then add parameters, then paste your parametorized query in the connection's properties. Here are the detailed steps for Excel 2010: Open Excel; Goto Data tab; From the From Other Sources button choose From Microsoft Query; The 'Choose Data Source' window will appear.

Oddly enough, the oldest method of MS-Query is the easiest, and doesn’t require any use of VBA. Its main drawback is that it supports only ODBC drivers, which involves a very old infrastructure that was first released in 1992. In addition, it doesn’t use Power Query technology and it’s ugly.

In Excel 2016, MS-Query can be found in the data tab.

Starting a query immediately hints at how old this UI is:

Select a DSN, and then select a table, columns and sorting order until the Query Wizard asks if you want to further edit the query:

Once inside Microsoft Query, you can do a lot of SQL editing without getting into SQL syntax. In the criteria tab, you may define filters to query and can specify that the filter values will come from parameters.

The notation <=[ToYear] means that the value is a parameter, and the prompt for this parameter will be ToYear.

Returning the query to Excel prompts the standard import dialog:

Click properties, and then Definition and Parameters.

You can link parameters to cells in Excel.

Ms Access Query Parameter Examples

You can also check the box that says Refresh automatically when cell value changes. Checking this option for more than one parameter means that the query may fire multiple times.

At this point, anytime that cells tied to a parameter change, the query will run and produce new data. The end result should be similar to thisfile.

Next week: A newer method for running an SQL query with parameters.

Date Parameters In Microsoft Query Wizard Download

About the author:

Access Parameter Query Between Dates

Dany Hoter is a renowned Excel guru who works as a solutions architect at DataRails. Dany has over two decades’ experience working as an expert for the Microsoft Excel team, and his online Excel classes reach over one million students across the globe. Fistful of frags server commands bedwars.