In the Query Parameters dialog box, in the Parameter column, type the prompt for each parameter for which you want to specify the data type. Make sure that each parameter matches the prompt that you use in the Criteria row of the query design grid. In the Data Type column, select the data type for each parameter.
Microsoft Access: Using Queries and Combo Boxes to Filter Reports - Duration: 9:51. RLFTechSolutions 167,943 views. Apr 17, 2018 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. The 'Enter Parameter Value' dialog box.
I have a query that's rather large, joining over a dozen tables, and I want to pull back records based on an id field (e.g.: between nStartID and nEndID).
I created two parameters and tested them as criteria and they work fine.
The issue is, I need to run an insert query from this main query, and need the parameters where they are, in the main query. So, I need to pass parameters to it programmatically.
Anyone have a clue as to how this can be done?
Thanks.
Gord Thompson
5 Answers
Microsoft Access 2013 Query Parameters
I just tested this and it works in Access 2010.
Say you have a SELECT query with parameters:
You run that query interactively and it prompts you for [startID] and [endID]. That works, so you save that query as [MemberSubset].
Now you create an UPDATE query based on that query:
You run that query interactively and again you are prompted for [startID] and [endID] and it works well, so you save it as [MemberSubsetUpdate].
You can run [MemberSubsetUpdate] from VBA code by specifying [startID] and [endID] values as parameters to [MemberSubsetUpdate], even though they are actually parameters of [MemberSubset]. Those parameter values 'trickle down' to where they are needed, and the query does work without human intervention:
Gord ThompsonGord ThompsonTry using the QueryDefs. Create the query with parameters. Then use something like this:
Many thanks for the information about using the QueryDefs collection! I have been wondering about this for a while.
I did it a different way, without using VBA, by using a table containing the query parameters.
Ms Access Parameter Prompt
E.g:SELECT a_table.a_field FROM QueryParameters, a_table WHERE a_table.a_field BETWEEN QueryParameters.a_field_min AND QueryParameters.a_field_max
Where QueryParameters is a table with two fields, a_field_min and a_field_max
It can even be used with GROUP BY, if you include the query parameter fields in the GROUP BY clause, and the FIRST operator on the parameter fields in the HAVING clause.
You can also use TempVars - note '!' syntax is essential
lokiPlenty of responses already, but you can use this:
Patrick HonorezPatrick HonorezMicrosoft Access Query Parameter In List
