Macro for Parameter Query in Excel

[chouna]

When writing a macro for a parameter query in Excel pivot table, I can do this: “WHERE field = ?”
and I am given a prompt “Parameter 1” before running the pivot. I would like to customize the prompt but have not been successful. I tried “WHERE field = [Give Branch Name]” which works with MS Query but not in VBA. Alternatively, there is a PromptString property in VB but it works only for QueryTable (Parameters is not a member of PivotTable). How do I change the default prompt to something more instructive?

 

[ETID]

You have to concatenate a variable into the sql statement.

branch$="my_branch_name"

“WHERE field = " + branch$

 

[chouna]

Sorry ETID, I didn't quite get it. I tried concactenating the statement with "+" and with "&" but neither worked. What I really need is to be able to get a descriptive prompt when Excel asks for the parameter to be supplied by user, something like "Enter the branch code" instead of the non-descript "Parameter1" input box.

 

[ETID]

what is the entire sql statement?

 

[chouna]

I simplified the statement which is encased in a WITH - END WITH construct.

"SELECT sddsales.branch, sddsales.salesman, sddsales.channel, sddsales.qty, sddsales.value " & Chr(13) & "" & Chr(10) & "FROM sddsales sddsales WHERE Branch =?"

In MS Query, I could issue the statement "WHERE Branch = [Enter the branch code]" and that would work fine. I need to get this particular code to work on my macro.