Problem when importing data into excel pivottable: zero values

[fboehlandt]

Hi everyone,
I am running a query in an access database that consists of three tables labeled: ASSETS, RETURNS, ADMINISTRATIVE. The query looks as follows:

SELECT RETURNS.Codes, RETURNS.Fundname, ADMINISTRATIVE.Main_Strategy, RETURNS.MM_DD_YYYY, 
[COLOR=red]IIf(IsNull([ASSETS.Assets]), "0",[ASSETS.Assets]) AS Assets[/COLOR]
FROM ADMINISTRATIVE INNER JOIN (RETURNS LEFT JOIN ASSETS ON (RETURNS.Code = ASSETS.Code) AND (RETURNS.MM_DD_YYYY = ASSETS.MM_DD_YYYY)) ON ADMINISTRATIVE.Code = RETURNS.Code;

Which runs fine in access. The problem is, however, that I want to export the information to an excel-based pivottable. When I copy the query into the command text field of the connection properties and refresh the connection, I only receive nulls as values in my pivottable. I have narrowed down the problem to the IIF(IsNull() statement in the query. This must be causing problems when trying to export data to a pivottable. Is it possible that the IsNull function is unique to Access? What would be the alternative if so?

p.s. it is the purpose of the query to write a null when there is no entry in the Assets column (as there are more date entries MM_DD_YYYY as there are asset entries).

 

[fboehlandt]

I have narrowed down the problem even further. I am using a OLEBD connection to import the data into excel pivottable. As it seems the IIF(IsNull()) combo cannot be used here (although no error message pops up). It just returns zero values for all entries (apparently misinterpreting the IIF function). Any alternatives? (Nz wouldn't work either I'm afraid...)

 

[neileg]

You're running two threads on this!

 

[fboehlandt]

I know,but i cant seem to shut this one down. Just ignore this thread, I accidentally submitted it twice...

 

[raskew]

Click on your post

Go to the bottom

Select Edit

Select Delete

HTH - Bob