Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Showing one column in a Chart (2000)

    I have a date field, and wish to show information for a particular year and a particular month using a parameter, e.g. Year([StartDate])=[Which year?]. When I then build a Chart it shows all twelve months along the x-axis!! How can I tell Access to show only the month of interest? Andy.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Showing one column in a Chart (2000)

    Add a parameter for the month: Month([StartDate]) = [Which month?]

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Showing one column in a Chart (2000)

    Sorry, I've got that parameter as well. But when I use the Chart Wizard it uses the two parameters but shows me Jan, Feb, Mar, etc., along the x-axis. I only need to show the one column (for the month keyed in the parameter box)?!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Showing one column in a Chart (2000)

    Open the form or report that contains the chart in design view.
    Select the chart.
    Activate the Data tab of the Properties window.
    The Row Source property of the chart contains an SQL string.
    Could you copy that SQL string and paste it into a reply, please? Thank you.

  5. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Showing one column in a Chart (2000)

    TRANSFORM Sum([Salary]) AS [SumOfSalary] SELECT (Format([Expr1001],"MMM 'YY")) FROM [Year And Month Query] GROUP BY (Year([Expr1001])*12 + Month([Expr1001])-1),(Format([Expr1001],"MMM 'YY")) PIVOT Format([StartDate],"MMM") IN ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Showing one column in a Chart (2000)

    Thanks. The IN ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec") forces the query (and hence the chart) to return *all* months. If you take this out of the expression, it should work as you intended.

  7. #7
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Showing one column in a Chart (2000)

    Tried this and it comes up with The Jet Database does not recognise ['Which year?'] as a valid field or expression??

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Showing one column in a Chart (2000)

    But it worked OK before you removed the IN ( ... ) part? Strange. Try this:

    Open the query Year And Month Query in design view. Select Query | Parameters..., and enter [Which Year?] and [Which Month?] as parameters of type Long Integer. Close the query and confirm that you want to save modifications. Note: you must enter the parameters exactly as they occur in the criteria line.

    If that doesn't help:

    the form or report in design view again, select the chart, then click the Builder button (the three dots ...) in the Row Source property. If asked, confirm that you want to edit the query. You will see the crosstab query in design view. Select Query | Parameters..., and enter [Which Year?] and [Which Month?] as parameters of type Long Integer. Close the query and confirm that you want to save modifications.

    Hope this helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •