Excel Pivot table

[Muthu]

I used ODBC connection to pull the data for Pivot table. I
would like to use only Query wizard to set the filter
condition. I set filter using two values for any field(for
example, reporting date > '12/20/2003' or reportingdate
< '12/31/2003' and return to pivot table wizard. when I
want to go back and change the filter condition, I am
getting error such as 'This query can not be edited with
query wizard'. then it launches the MS Query. I don't want
to go to MS Query, I would like to use Query wiard
instead. Any soultions

Thanks

 

[Dick Kusleika]

Muthu

Sorry, no solutions. This is an effect of the screwy way that Excel and
MSQuery parse SQL strings, particularly compound criterion (same field in
more that one criteria).

When you set it up, it should look like this
Date Date

12/20/2003 <12/31/2003

Somewhere in the process, it gets changed to one compound criterion
Date

12/20/2003 And <12/31/2003

and the wizard doesn't know how to parse it, so it gives you that message
and makes you use MSQuery.

Here's an alternative for you to consider: Make the query a parameter
query. Edit the query in MSQuery and change the criterion to look like this

Date

[Enter Start Date] And <[Enter End Date]

replacing your literal dates with bracket-enclosed strings. This will tell
Excel to prompt you to enter those values whenever you refresh. The upside
is that you never have to edit the query to change those dates again. The
downside is that you will be prompted every time you refresh.

For more on parameter queries, see here

http://www.dicks-clicks.com/excel/ExternalData6.htm