AutoCalc - Pivot Charts - difference between Table and Query

[krishnak]

This refers to my earlier post on the "AutoCalc option grayed out ...".

I am experimenting with different scenarios. I want to point out that I am having this problem working with a query with calculated fields from a table, which forms the input data.

When I work with the table itself (containing the input data), in the Pivot Chart view, I am able to view all the options in the AutoCalc menu. I can choose Average or Sum or Max etc.

This is strange. The option for a query based on this table is only Count.

Has anyone noticed a similar problem?

Once a Pivot chart is created, the Legend options seem to be limited. For example, I cannot change the orientation of the Axis titles, insert values for the bars at their tips etc.

- Krishna

[OBP]

Krishna, I think it depends on the Type of fields that you select for inclusion. ie if you select only Values it allows arithmetic functions, but if you include text it only allows count.
Have you considered using a Query with Totals or a Crosstab Query with a Graph Form?

[krishnak]

OBP, that is a good point.

In my query, I am using a function to calculate the result. As I want to get a blank cell (instead of 0) if the input data is not entered, I am using vbNull. The function is as following:


'Find the Trips per Prime Mover
Function NumTrips(Trips As Variant, pmer As Variant) As Variant
On Error GoTo NextStep
If ((IsNumeric(Trips) = False) Or (IsNumeric(pmer) = False) Or (pmer = 0)) Then
NumTrips = vbNull
Else
NumTrips = (Trips / pmer)
NumTrips = Format(NumTrips, "#,###.000")
End If
NextStep:
If (ErrNumber <> 0) Then
Exit Function
End If
End Function


Could this be causing the problem?

I do not want a 0 to appear in the query results for a non-entry in the input data table. Leaving the cell blank will not count it for calculating the Average value of a range of results.

-Krishna

[OBP]

Krishna, can you exclude Non entries all together by using a pre-query?

[krishnak]

How do I do that? Will you pl elaborate?