We help IT Professionals succeed at work.

Improper Number Format in Access Query Does Not Display in Pivot Table

I am using a query in Access to feed a pivot table. I am using iif() to do several conditional sums across 5 columns based on 1 column of values. The first 3 added up fine and work great in my pivot table. But my last 2 appear left aligned in my query (the first 3 are right aligned)and then do not show up at all in my pivot table. I tried using clng(), but still nothing. When I am on those 2 columns, there are no values for me to select in the "Format"  section of the properties in Access either. Again the other 3 are working just fine. I cannot figure it out since I am summing on the same column based on different conditions. What is happening?
Comment
Watch Question

Most Valuable Expert 2012
Top Expert 2013
Commented:
Try wrapping your entire expression in

CDbl()   '<--- for floating point
or
CInt()   '<-- for integer


To explicitly convert the output of you IIF to a numeric format.

Author

Commented:
That was fast! And that worked great, except for now my pivot table errors out after refreshing about 100k records. The source is about 2.9M records. Is there something I can check?
Most Valuable Expert 2012
Top Expert 2013

Commented:
Can you post a screeshot showing the error?
Most Valuable Expert 2012
Top Expert 2013

Commented:
Also, does your iif return any nulls or text values?

To handle nulls you could do something like this:

Cdbl(Nz(your expression,0))

That will convert any nulls to zero to avoid errors when making the conversion to a numeric type.

Author

Commented:
There is not any text. But there is nulls which I set to "0" if false, but I will try CDbl. I am having problems with my work computer so I cant post the screenshot ATM. But it says "Make Sure database is available" and "Problems obtaining data"
Most Valuable Expert 2012
Top Expert 2013

Commented:
That sounds like a connection issue - not an issue with your query.

Author

Commented:
This worked great. Thanks. I did not have to use the CDbl and Nz. I was just having issues with connection like you suggested.