MS Query - Parameters

[mattydalton]

Hi

Is it possible to set parameters on a query but use a wildcard to return all instances? I have a query that I want to be able to set multiple parameters on but give the user the ability to select as many or as few parameters as they want to see. 2 of the parameters are number fields and 2 are text fields with no spaces.

Thanks

Matt

[NBVC]

Can you elaborate?

[mattydalton]

If a user wants to define only one of the parameters (for example the year in which a portfolio was bought) but wants to leave the others blank (for example the name of the client, portfolio ID and a category) then the data returned will be for all clients, all portfolios and categories but puchased in the specified year. Likewise, the user might want to return data for a specific client irrespective of the other parameters.

[NBVC]

In the MSQuery application set all your paremeters with the Like[] condition

e.g

Like [Client]

when you close the Query fill in the parameter input boxes with a temporary filter item...

then in the sheet, say use input cells for your users to input desired filter criteria..

Then use other cells somewhere else in sheet to reference these cells respectively and enter formulas like: ="%"&A1&"%" where A1 is a cell that user inputs his/her parameter.

Then right-click on the query output and select Parameters

Select one parameter from the left pane and then select "Get the value from the following cell" and enter the cell where you put the above formula.

Check the "Refresh automatically when cell value changes checkbox".

Repeat for each parameter.

Click Ok and test by entering desired parameters in any/all input cells.

[mattydalton]

Sorted, thanks