Results 1 to 11 of 11
  1. #1
    Kirsti is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Location
    New Zealand
    Posts
    172

    Left Hand column in chart empty

    Hi, please see the report in the database attached.



    Can anyone let me know why the left hand column is empty? And how I fix this issue?

    Any help appreciated.

    Kirsti
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Need the data to test graph.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Kirsti is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Location
    New Zealand
    Posts
    172
    Hi June,

    The chart is based on the queries: TTRS2_YTD2 and TTRS2_YTD4

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The tables are links to another db (backend?). Don't have data in the posted db.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Kirsti is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Location
    New Zealand
    Posts
    172
    Apologies. Try this one.
    Attached Files Attached Files

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You have records with no value in Mediation Outcome and Tribunal Outcome. These Nulls do not show. Handle the Nulls with Nz in the query.

    Tribunal Outcome: Nz([Tribunal_Outcome_TT],"None")

    Or Exclude the Null records with criteria: Not Is Null
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Kirsti is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Location
    New Zealand
    Posts
    172
    Thanks for your input June,

    The issue I have is that I need those records with no value in the Mediation and Tribunal Outcomes. Reason being that I have the Housing_Officer table in the query as a way to ensure that a name will appear in the chart series, even if they have no Mediation or Tribunal Outcomes against their name.

    So, I tried as you suggested, but that still has an empty column on the chart with an axis label of "none". And if I exclude the records with Null value, I loose the names of the Housing Officers that I need to appear in the chart legend.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes that is a conundrum. The blank has to show somewhere. You could use Count instead of Sum and show bars on the graph for the None value.

    Correct my earlier statement. The Nulls are showing in the graph. That is the lefthand space with no caption and sums of 0.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Kirsti is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Location
    New Zealand
    Posts
    172
    Thanks June,

    I found this code, obviously it is written for charts in excel, and as you know I know nothing about VBA! But would it be possible to manipulate this code to use it within the report?


    Delete or Remove Empty Columns
    Description: Use this macro to delete/remove empty/blank columns with in the Selection.
    Code: Sub DelBlankColumns()
    ' Deletes all empty columns on the active worksheet
    Dim iCol As Integer
    With ActiveSheet.UsedRange
    For iCol = .Column + .Columns.count - 1 To 1 Step -1
    If IsEmpty(Cells(65536, iCol)) And IsEmpty(Cells(1, iCol)) Then
    If Cells(65536, iCol).End(xlUp).Row = 1 Then
    Columns(iCol).Delete
    End If
    End If
    Next iCol
    End With

    End Sub

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    No - this code is actually removing a column of cells from the worksheet, thereby removing blank cells from the data range so they aren't represented in the plot.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Kirsti is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2012
    Location
    New Zealand
    Posts
    172
    Ah, ok. Thanks - I will keep looking!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Add Error bars to column chart
    By kzimm14 in forum Reports
    Replies: 7
    Last Post: 02-02-2012, 01:09 PM
  2. Replies: 4
    Last Post: 11-20-2011, 01:08 PM
  3. Multiple Left Joins From Same Column
    By x0200196 in forum Access
    Replies: 1
    Last Post: 09-08-2011, 10:14 AM
  4. Empty Column in Query
    By sesproul in forum Queries
    Replies: 3
    Last Post: 01-18-2010, 06:04 PM
  5. Column Chart Issue
    By Sim_ in forum Access
    Replies: 0
    Last Post: 10-26-2009, 01:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums