Odd behavior with PivotTable showing zero instead of the value

[cisco55]

Greetings. For a couple years I have been an avid visitor, but I just this moment registered, as the issue is still completely unresolved. Thus, my first post.
(The only forum I trust about MS Excel on the Internet is this one. Mr Excel, you are wise and well-respected in my workbook.

My problem is this:

I use Excel 2010 and have Pivot Tables that are using a Data Connection which is being built (and rebuilt) with VBA.
The Pivot Table field is calculating a field that contains an integer value. The Pivot Table field filter properly displays this value.
However, the Pivot Table itself calculated Sum field only displays 0 (aka zero). Other fields that are exactly the same as this one are displaying properly. Mystifying.

I am assuming you will want to see a little code.
My data connection is built as follows:

ThisWorkbook.Connections.Add "ChartQuery", "", "OLEDB;Persist Security Info=0;DSN=Excel Files;DBQ=" & wbFullName & _        
";DefaultDir=" & wbPath & ";DriverId=1046;MaxBufferSize=1024;PageTimeout=5;BackgroundQuery:=False", sSQL, 2

(And yes, my sSQL is sound, there are no typos, no special character issues)

Keep in mind that the filter in the Pivot Table shows my value (for example, 14) however it always only displays a zero

My Pivot Table is formatting the field (like the others just like it) as a Number, two decimals
My Pivot Table data source is ChartQuery
The worksheet this is pulling from is also set to use Number format for the entire column, but changing that doesn't make any difference even on the other fields that are working.

Perhaps this is enough information to get us started. Please ask the questions that you may.
I've done everything but open my computer case looking for the bug or mistake here.

Thanks
Francisco Shillander

 

[Ever]

For anyone stuck on a similar issue, multiplying the data referenced by 1 fixes the issue of the pivot table treating the figures as string text. Simply add *1 at the end of the formula of the table your pivot table is linked to.