Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Location
    Alaska
    Posts
    19

    Question Unanswered: Specifying a DateRange for a Chart???

    I've created a chart based upon a query. I know how to set the criteria for a specific year inside the query, but would also like to allow the user to specify his own date range.

    My chart doesn't have any date field on it, and hence I'm not sure how to Link the Child/Master fields to my form to pick only data from a range of dates.

    Any ideas would be welcomed and appreciated!

    Thank you...

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Use a form

    You can use a form to gather information prior to printing the report.

    On this form have 2 unbound controls with say txtDateFrom and txtDate to to allow the user to input their required dates.

    Also have a button on the form "Print Chart / Report" which opens the report.

    Incorporate error checking on the form to ensure the dates entered are to your requirements (this can be done within the onClick event of the print chart / report button)

    Within the query, base the criteria on the values on the form rather than hard - coded i.e.
    Instead of ">= #01/01/03# and <=#10/01/03#"
    >= forms!frmMyform!txtdatefrom and <=forms!frmMyform!txtdatefrom

    Also you should check that this form is open when the report opens

    Dev Ashis has a great Access site with an example of how to do this;

    http://www.mvps.org/access/forms/frm0002.htm

    Hope this helps
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Dec 2002
    Location
    Alaska
    Posts
    19

    Re: Use a form

    Thank you for your reply. Your answer did the trick. One of the problems that I discovered has left me with another unsolved mystery....I can only designate two fields for the Chart query when doing using a Date Range query. I'm not sure why this is the case.

    My other charts which didn't have a Date Range set, would allow me to use three categories for the chart.

    This is the Row Source property from the DATE RANGE querybyform, as you suggested. It works great. (But, it limits me to two fields)

    SELECT [ObjPartCodeText],Sum([CountOfObjPartCodeText]) AS [SumOfCountOfObjPartCodeText] FROM [qryChartFailureCountByDateRange] GROUP BY [ObjPartCodeText];

    ---------------------------------------------------------------------------

    This is the Row Source property from a report that is based upon a query where the criteria is set by year, i.e., "2002". It works fine when based on a single date value.

    TRANSFORM Sum([CountOfObjPartCodeText]) AS [SumOfCountOfObjPartCodeText] SELECT [ObjPartCodeText] FROM [qryChartFailureCountByYear] GROUP BY [ObjPartCodeText] PIVOT [EquipmentCategory];

    I created both Charts using the Wizard.

    So, bottom line is when I tried to duplicate the above mentioned chart using a Date Range, it didn't work. I don't understand why.

    Using the Chart Wizard, it asks for 3 categories.....Data, Axis & Series. When using the querybyform, to set a Date Range, it refuses to produce a chart when I set all three fields. It gives me an error: The Microsoft Jet Database engine does not recognize '[forms]![frmChartByRange]![DateFrom]' as a valid name or expression.

    I'm not sure if there's a simple answer as to why this won't work. It's not that big of deal to me. I can create a field on the report to hold the "EquipmentCategory" value. But, it is baffling to me.

    Thanks again for your help in working with these charts.

    Cheers!

    Originally posted by garethdart
    You can use a form to gather information prior to printing the report.

    On this form have 2 unbound controls with say txtDateFrom and txtDate to to allow the user to input their required dates.

    Also have a button on the form "Print Chart / Report" which opens the report.

    Incorporate error checking on the form to ensure the dates entered are to your requirements (this can be done within the onClick event of the print chart / report button)

    Within the query, base the criteria on the values on the form rather than hard - coded i.e.
    Instead of ">= #01/01/03# and <=#10/01/03#"
    >= forms!frmMyform!txtdatefrom and <=forms!frmMyform!txtdatefrom

    Also you should check that this form is open when the report opens

    Dev Ashis has a great Access site with an example of how to do this;

    http://www.mvps.org/access/forms/frm0002.htm

    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
  •