Date Parameters In Microsoft Query Wizard

You have to make sure the Parameters!Parameter1.Value is a date. So, when you using string as parameter type you have to write like this: =format(cdate(Parameters!Parameter1.Value), 'dd/MM/yyyy') And then, if your dataset need a string parameter, you can just set this in the parameter tab in the dataset: =format(cdate(Parameters!Parameter1.Value), 'dd/MM/yyyy') But if the dataset expect a date time parameter. Re: reference a date in microsoft query wizard If you select proper table and field - Date you should be able to select equal then date ( not today ) or Preview filter.jpg.

MS Access Tutorial

MS Access Useful Resources

Selected Reading

The best part about queries is that you can save and run the same query again and again, but when you run the same query again and again by only changing the criteria then you might consider the query to accept parameters.

If you frequently want to run variations of a particular query, consider using a parameter query
Parameter query retrieves information in an interactive manner prompting the end user to supply criteria before the query is run.
You can also specify what type of data a parameter should accept.
You can set the data type for any parameter, but it is especially important to set the data type for numeric, currency, or date/time data.
When you specify the data type that a parameter should accept, users see a more helpful error message if they enter the wrong type of data, such as entering text when currency is expected.
If a parameter is set to accept text data, any input is interpreted as text, and no error message is displayed.

Example

Let us now take a look at a simple example by creating a parameter query. Let us open your database and select Query Design in the Create table tab.

Double-click on the tblProjects and close the Show dialog box.

Select the field you want to see as a query result as shown in the following screenshot.

In the query design grid, in the Criteria row of the ProjectStart column, type [Enter a project start data]. The string [Enter a project start data] is your parameter prompt. The square brackets indicate that you want the query to ask for input, and the text is Enter a project start data is the parameter prompt displays.

Let us now run your query and you will see the following prompt.

Let us now enter the following date.

Click OK to confirm.

As a result, you will see the details of the project which started on 2/1/2007. Let us go to the Design View and run the query again.

Enter the date as in the above screenshot and click Ok. You will now see the details of the project which started on 5/1/2008.

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

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.

Microsoft Query Wizard

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:

Access Parameter Query Between Dates

Click properties, and then Definition and Parameters.

You can link parameters to cells in Excel.

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.

About the author:

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.