MSQuery using parameters

Question

I am writing a query using MS query in Excel. I know that in the last step of the query wizard I can go to the MS Query window and put the parameters in there but if I later want to edit the parameters I don't know how to get back in. Also, if a user runs the query from Excel, it flashes up the box and prompts for input but how would the user return all records? Thanks.

Answer 1

sorry - can't attach screen shot. I'll keep playing :) thanks for your help.

Answer 2

It says invalid use of [] - here is the screen shot..

thanks

Answer 3

I created a table like you specified and ran the query which you typed. It worked for me. When I didn't supply any parameter, it simply return all values when I pressed OK..

Only difference, I can notice in MS QUERY is this.

While in SQL it is - like '%' & ?

but in Criteria field it shows - like '%' & []

Hence, if you writing something in criteria line, you will need to write like above.

If I write [] in place of ? in SQL, [] gets replaced by ? in SQL automatically. But [] remains as it is in Criteria field. Hence, probably you need to replace your criteria with

like '%' & [] which is essentially replace ? with []

Not sure, if this is the problem?

Answer 4

Hi - that doesn't work for me. at the moment I have [enter area] in the criteria line, when the query runs it prompts for the user to type in an area name. I have changed the command text in the Connection properties in msquery to...

SELECT November Sales.Sale Person, November Sales.Area, November Sales.Sales

FROM November Sales November Sales

WHERE (November Sales.Area like '%' & ?)

and it lets me type in a parameter but if I leave blank it says empty value for parameter

thanks, hope you can help.

Answer 5

You need to replace = by Like '%' & ? in your where clause...For example

WHERE (Inspection_Date.Issues = ?) should become

WHERE (Inspection_Date.Issues Like '%' & ?)

Hence, if an user doesn't enter anything and presses OK, it will return all values.