excel link with access differs from simple export from access

[M0RDANT]

I have a wierd one. I am running a query in Access 2010. This query is using an IIF function in the expression. The query runs great. So I have an Excel spreadsheet which I want to link to the query. I link great, it pulls in the data; however, in cases which would trigger the IIF function, those are not polling correctly.


I have multiple sheets in the workbook and have use the IIF feature on those as well (different lookup) and they work fine.
If I export the data straight to excel (no linking) from access all data is correct.

What gives??

Rich

[rpeare]

Can you give an example of the data that and the iif statement that is working vs the iif statement that is not working?

You may be using a name that is reserved in Excel that is not reserved in Access.

Also what exactly are you expecting to see and what do you actually see? is the field coming up blank or with some sort of error message? Is the field in excel formatted correctly to show the data from Access (for instance if you're linking a date field and you have 7/12/11 you may get a number rather than a date.

[kevins]

I don't have an IIF feature in my query, however I am trying to accomplish the same thing that you have accomplished. I am not an expert with writing VBA or macros, so I am just trying to figure it out by trial and error. I have a query that I want to link to an existing excel spreadsheet. Can you help?

[eugenerobitaille]

Hello Mordan,
I have the same problem with iif statement and it's a bug. My iif statement is really simple.
Example:
Releve: IIf([EndroitEntrepose] Like "*without*","without","with")
I can get the appropriate result in Access, but when I get the data into Excel, all my lines will give me "with" value witch means the IIf statemment always give a "false" response to the iif statement. Keep me posted if you have an answer....

[heidiatmn]

I have a wierd one. I am running a query in Access 2010. This query is using an IIF function in the expression. The query runs great. So I have an Excel spreadsheet which I want to link to the query. I link great, it pulls in the data; however, in cases which would trigger the IIF function, those are not polling correctly.
I have multiple sheets in the workbook and have use the IIF feature on those as well (different lookup) and they work fine.
If I export the data straight to excel (no linking) from access all data is correct.

What gives??

Rich

Have you found a way around this? I am having the same problem.