Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crosstab Query(?) chart (2007)

    I'm trying to create a chart, that shows only the data relating to criteria from a form.

    I've done a summary chart successfully, but the detail one is ignoring the criteria, or produces an error when I try to add [form]![frmrptSel]![etc... as criteria. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    Attached is a partial version of the WIP, that opens with the relevant form.

    I could do it via VBA (eventually) but I'm hoping there's a simpler method.

    TIA Paul
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Crosstab Query(?) chart (2007)

    Hve you defined the form variable as a parameter in the crosstab query, just a gues.

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

    Re: Crosstab Query(?) chart (2007)

    You have to declare parameters in a crosstab query:
    - Open the report in design view.
    - Click on the chart.
    - Click in the Row Source property in the Data tab of the Properties window.
    - Click the ... button on the right hand side.
    - Add the criteria.
    - Select Query | Parameters...
    - Enter the first parameter exactly as used in the criteria, and set its data type to Date/Time.
    - Repeat for the other.
    - Click OK.
    - Close and save the SQL statement. It will become something like this:

    PARAMETERS [Forms]![frmRptSel]![txtDtFrom] DateTime, [Forms]![frmRptSel]![txtDtTo] DateTime; TRANSFORM Sum(tblMedi.Count) AS SumOfCount SELECT (Format([MonthYr],"mmm"" '""yy")) AS Expr1 FROM tblMedi WHERE (((tblMedi.MonthYr) Between [Forms]![frmRptSel]![txtDtFrom] And [Forms]![frmRptSel]![txtDtTo])) GROUP BY (Year([MonthYr])*12+Month([MonthYr])-1), (Format([MonthYr],"mmm"" '""yy")) PIVOT tblMedi.Reason;

    You can also save this as a stored query, and use the stored query as Row Source for the chart. See attached version.
    Attached Files Attached Files

  4. #4
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query(?) chart (2007)

    Ah, this just goes to show how many crosstabs I've done; I'd totally forgotten about parameters.

    Thank you very much.

Posting Permissions

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