CODEWITHSUNDEEP
excelms-access
iDevlop
iDevlop

Reputation: 25262

Access query returns a different result when read from Excel

I have a query in an ACCDB that works fine in Access.
I can successfully copy/paste its data to Excel.

However, from Excel, if I try to insert a Pivot Table using External Data Source, pointing to the very same query, then some numeric fields have weird formatting and some calculated numeric columns (formula in the query) have their value divided by 100 compared to the source.
Never seen that behaviour. Any suggestion ?
The whole MS-Office setup is in 2010.
What I have already done in the source query (without visible improvement):

The behaviour is exactly the same on other PCs in the same bank.

Upvotes: 1

Views: 312

Answers (2)

Gusano
Gusano

Reputation: 1

Use the query in Access to first Make Table in Access then import the table to excel.

Upvotes: 0

iDevlop
iDevlop

Reputation: 25262

I could solve the problem which was due to 2 different bugs, probably in JetOLEDB.

  1. Like is not handled properly by Excel
    The query contained some formulae using Like:
    iif(someField Like "XX*";0;anotherField).
    Changing this to iif(Left(somefield;2) = "XX";0;anotherField) solved calculation differences between Excel but and Access.
  2. Reference to another calculated column is handled differently
    Say you have 2 query columns:
    Rate: i.Rate *100 (i is a table alias)
    Amount: Rate*Price
    Access calculates Amount using the Rate calculated column, while Excel uses the Rate field from table i.Therefore I had to change the Amount expression to:
    Rate: i.Rate *100
    Amount: i.Rate *100*Price
    since Excel does not seem to make always use Rate from the table (i.Rate).

Upvotes: 1

Related Questions