Pivot Chart Configuration won't remain static. (1 Viewer)

[Zorkmid]

Hi all,

I have a subform based off a query that displays data in a pivot chart. In the pivot table view, I have it set up to give me the count of records per months over time for several categories, displaying in a line graph per month.

The first time I use it, it works perfectly. But sometimes it changes the way the pivot chart displays the data.

For example: instead of giving monthly data, will aggregate all of 2007 and 2008 into one point and then then display monthly data for 2009.

Whenever I want to run it again, I have to go into pivot table view and expand the "date by month" fields down to the month level. Is there a way to "lock" it? I can't figure out why this happens.


-Z

 

[Zorkmid]

Bump. This is so frustrating

 

[ChrisO]

Zorkmid.

I think to have a reasonable chance of solving this problem you will have to: -

Post a demo database with enough data to show the problem.
Post it in the earliest Access version you can.
State how to simulate the problem.
State the regional settings you are using.

Regards,
Chris.

 

[Zorkmid]

Unfortunately I won't be able to do this as this query is ultimately based off of a table that is full of personal info. Maybe I can describe the problem better?

I create a form that defaults to pivotchart view, and pull in relavant fields in form view. I use a pivot chart view to pull Month and Year fields into the row fields on the left and I pull a field of incident types onto the column field. Pull the primary key field into the middle, and calculate the count, and hide the details.

I then expand the all year fields so that "Month" is the unit that the count is totalled for.

I then close the form. Reopen it and view it in pivotchart view. I set it up as a line graph, and do a legend etc. etc. Then I close it.

I open a filter form that I have made, and pull this new form thatjust built into it.

It seems to work fine at first. But sometimes when I filter in such a way that there are months that have no records, it screws up the way the pivot chart was set up. For example: Instead of displaying a point on the line graph for every month from 2007 to 2010, will display a total for 2007, 2008 and then every month for 2009 and then one total for 2010.

Is there a way I lock the pivotable the way I had it set up?





Also, you mentioned regional settings. Not sure what those would be.

-Z


If there's something else I can do to explain this better, please ask

 

[Zorkmid]

Something else that may be of note.

I run many charts like this, columnar charts don't suffer from this at all. Just the line graph I described above.

-Thanks

edit:

Actually there is a similar error here as well. If I run the filter and there is a zero, or null in any of the fields, then then next filter I run that includes it, I'll lose any formatting I'd done, like changing the color or showing the value above the bar.

 

[Zorkmid]

I'm going to try so set all the nulls in my pivot table to zero. Maybe that will preserve my formatting.

 

[Zorkmid]

No dice, back to the drawing board.

 

[ChrisO]

Perhaps you could just work on a copy of the original Form.

Option Explicit 
Option Compare Text 

Public frmFormCopy As Access.Form 

Sub ForTestOnly() 

    OpenFormCopy "My Graph Copy" 

End Sub 


[color=green]' Please do NOT call this procedure from the original Form. 
' We only want to open a copy of the original Form.[/color] 
Public Sub OpenFormCopy(ByVal strFormCaption As String) 

    [color=green]' Here, frmGraphOriginal is the 
    ' literal name of the original Form.[/color] 
    Set frmFormCopy = New Form_frmGraphOriginal 

    With frmFormCopy 
        .Caption = strFormCaption 
        .Visible = True 
    End With 

End Sub

If that doesn’t work as required then I’ll need you to create the chart from scratch.
The reason is that I could create many charts and they may not produce the same problem.

Regional settings, as displayed in control panel, can interfere with dates.