Can not use query edit and parameter in Excel of office365

Question

Hi,

can you please advise why I can not edit query and parameter? thanks.

Regards

Steven

Answer 1

Hi Steven,

Glad to hear that you found a workaround.  I’d like to write a summary to help other members easily see the information.

Issue description:

1. Using an OLE DB Query, the Edit Query button is greyed out.
2. Using an OLE DB Query, entering a query that uses a question mark (?) as a parameter on the Command text shows an error indicating you need to set the parameter.

Solution:

1. If you are using an OLE DB Query, you can change the query via the Command text area. The Edit Query button is available if you are using a Microsoft Query.
2. If you are using an OLE DB Query, it is not feasible to enter a question mark (?) as a parameter. As a workaround, you can use a Microsoft Query. You can then set the parameter using a question mark (?) in the Command text area.

Thanks,

Sheen

Answer 2

Hi Sheen,

I had been using that.

my question is I can not use "?" or parameter.

I can use select * from AA_OpenJobView, or select * from AA_OpenJobView where job_date > '2013-1-1'.

but I can not use select * from AA_OpenJobView where job_date =?. I want to let user enter the condition when they refresh the data.

error message is at least one parameter is not assigned.

Regards

Steven

Answer 3

Hi Steven,

As the error implies, you need to set a parameter for the query. It is not feasible to use a question mark (?).

The error I get using the English version of Excel might help better:

 

Given the situation, we suggest posting your feedback to our Excel UserVoice;  this is the best way to make our products and services better for you and others.

Regards,

Sheen

Answer 4

Hi Sheen,

I found the reason, have to use Microsoft query, then I can use parameter with ?.

Regards

Steven