Question Access Query, Excel Pivot, and Blank Cells

[md57780]

I have an Access Query that returns a calculated field. This query feeds into an Excel pivot table. When this calculated field is used as a Value field in the pivot, the pivot table correctly displays the number of entries where the calculated value is not empty. However, when i dbl-click the value on the pivot to get the field detail records, it returns records where the field is both non-empty and empty.

My query calculation is as follows:

Flag_INV: IIf([I].[File Rcvd]<=[d].[thisDate] And [I].[FileClosedDate]>[d].[thisDate],'INV',Null)

If i had 5 records, and 3 of them returned INV and 2 returned Null, the pivot table would show 3, as it should when set to Count. When i dbl-click the field, it populates a new sheet displaying all 5 records.




I have also tried the following:

• return a blank string "" instead of Null
• return a 1 instead of 'INV'
• setting the Format property of the calculated field to numeric
• setting the pivot to the query directly
• setting a table to the query, and setting the pivot to the table

How do I need to set the return of this calculated field from the query so that the Excel Pivot will only display 3 records when I drill down to the record detail? Or is this possibly an issue on the Excel side?

Appreciate any insight into this issue...


Access 2007 in .mdb format
Excel 2007 in .xlsx format

The full Access Query

SELECT D.thisDate AS AsOf, I.*, IIf([i].[filecloseddate]=[d].[thisdate],DateDiff('d',[i].[file rcvd],[i].[filecloseddate]),Null) AS DIP_CLSD, IIf([I].[File Rcvd]>[d].[thisdate],Null,DateDiff('d',[I].[File Rcvd],IIf([d].[thisdate]>[i].[filecloseddate],[i].[filecloseddate],[d].[thisdate]))) AS DIP_ALL, IIf([I].[File Rcvd]<=[d].[thisDate] And [I].[FileClosedDate]>[d].[thisDate],"INV",IIf([I].[FileClosedDate]=[d].[thisDate],"CLSD",Null)) AS Category, IIf([I].[File Rcvd]<=[d].[thisDate] And [I].[FileClosedDate]>[d].[thisDate],"INV",Null) AS Flag_INV, IIf([I].[FileClosedDate]=[d].[thisDate],"CLSD",Null) AS Flag_CLSD
FROM qry_Dates AS D, (SELECT Q1.* FROM qry_ActiveInventory AS Q1 UNION SELECT Q2.* FROM qry_ClosedInventory AS Q2)  AS I
WHERE (((IIf([I].[File Rcvd]<=[d].[thisDate] And [I].[FileClosedDate]>[d].[thisDate],"INV",IIf([I].[FileClosedDate]=[d].[thisDate],"CLSD",Null))) Is Not Null));