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. Please refer the following query: SELECT Date, Time FROM Table WHERE (DATEDIFF(D, Date, @Date) microsoft.com. Regards, Fanny Liu. I created the query with the query wizard and set it as the record source for my report. I tried with the '#' but still no luck. I've tried to execute the query through the VBA but I need that query to exist at all time in order for the cross tab query to work. – d9120 Jun 4 '12 at 14:15. 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.
- MS Access Tutorial
- MS Access Useful Resources
Microsoft Query Parameters Excel
- 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.
Date Parameters In Microsoft Query Wizard Word
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.