Powerpivot won't import Access query data but it sees the query

[pete234]

Well this is strange as I had tested linking to queries a week ago.
Now I made a query in Access very simple select and I linked to the query but it keeps returning 0 rows.
It sees the query or the headers are there but refresh is "zero rows transferred."
I closed out of everything went back in same thing.
So is there some setting I could have hit that is preventing the transfer of data?
Just when I think something will be 5 minutes...any help appreciated.
Now I noticed a couple of smaller queries using same table aren't showing data either.
Is there some type of table it won't work with? It's just an employee table.

 

[miguel.escobar]

are you trying to import a query or typing the query on the powerpivot window? Can you try create the query in access and then import that query using powerpivot?

 

[pete234]

I created the query in Access and I'm just trying to import the query to filter off employees I don't need in the report instead of filtering in pp.
The query is working in Access. Looking at the SQL in powerpivot it's just SELECT [Query]* FROM [Query].
A much larger query imports fine.

 

[miguel.escobar]

weird...try writting the query inside Powerpivot and see if that imports the rows

 

[pete234]

Ok I compared it to a query that was similar but working and...Powerpivot doesn't like LIKE or the wildcard.
I took out the Like "xyx*" part of the query and now it works.

 

[MD610]

Try using LIKE 'xyz%' in the WHERE clause when writing the SQL code directly in PowerPivot where single quotes are used instead of double and % is the wildcard.

Access has a slightly different "flavor" of SQL than SQL Server does. There are some syntax variations between the 2 as well as some different functions. PowerPivot uses the SQL Server version even when querying a table stored in Access.