Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Limit chart to date range based on a form's textboxes

Discussion in 'Information Technology' started by Art Marks, Nov 7, 2005.

  1. Art Marks

    Art Marks
    Expand Collapse
    Guest

    I'm currently opening a report where the date range is limited based on the
    values of 2 textboxes on a form using code like this:

    Private Sub cmdOpenrptWeight_Click()

    DoCmd.OpenReport "rptWeight", acPreview, , "[Date]>=#" & TxtMinDate & "#
    and [Date]<=#" & TxtMaxDate & "#"

    End Sub

    Problem is that the charts at the end of the report aren't affected. I'm
    using an SQL SELECT statement for the RowSource of the charts.

    Any solutions? Can I design the charts with the RowSource property set and
    then modify that property in the sub shown above to include a WHERE clause
    with the [Date] limited?

    Thanks
    --Art
     
  2. Loading...

    Similar Threads Forum Date
    Opinion The Limitations of Being ‘Spiritual but Not Religious’ Breaking News Mar 23, 2013
    What actually are the 3 worlds? And is Maya limited only to the physical plane? Questions and Answers Nov 30, 2012
    Legal Cash Limit Breaking News Apr 4, 2012
    India Govt Mulls Proposal to Limit Number of Guests at Weddings Breaking News Feb 22, 2011
    The Limits of Free Will Interfaith Dialogues Feb 7, 2011

  3. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    Yes, you can set the rowsource property of the report to a query which reads
    the dates from the same form. Use the build button (right-click in the
    criteria box) Manually, you can just add the form references to the criteria
    box, like:

    Between [Forms]![YourFormName]![txtMinDate] And
    [Forms]![YourFormName]![txtMaxDate]
    --
    Arvin Meyer, MCP, MVP
    Microsoft Access
    Free Access downloads
    http://www.datastrat.com
    http://www.mvps.org/access

    "Art Marks" <Xarthurruhtra@netscape.netX> wrote in message
    news:#ZQgkmw4FHA.3348@TK2MSFTNGP10.phx.gbl...
    > I'm currently opening a report where the date range is limited based on

    the
    > values of 2 textboxes on a form using code like this:
    >
    > Private Sub cmdOpenrptWeight_Click()
    >
    > DoCmd.OpenReport "rptWeight", acPreview, , "[Date]>=#" & TxtMinDate &

    "#
    > and [Date]<=#" & TxtMaxDate & "#"
    >
    > End Sub
    >
    > Problem is that the charts at the end of the report aren't affected. I'm
    > using an SQL SELECT statement for the RowSource of the charts.
    >
    > Any solutions? Can I design the charts with the RowSource property set and
    > then modify that property in the sub shown above to include a WHERE clause
    > with the [Date] limited?
    >
    > Thanks
    > --Art
    >
    >
     
  4. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Sun, 6 Nov 2005 14:38:20 -0500, "Art Marks"
    <Xarthurruhtra@netscape.netX> wrote:

    >I'm currently opening a report where the date range is limited based on the
    >values of 2 textboxes on a form using code like this:
    >
    >Private Sub cmdOpenrptWeight_Click()
    >
    > DoCmd.OpenReport "rptWeight", acPreview, , "[Date]>=#" & TxtMinDate & "#
    >and [Date]<=#" & TxtMaxDate & "#"
    >
    >End Sub
    >
    >Problem is that the charts at the end of the report aren't affected. I'm
    >using an SQL SELECT statement for the RowSource of the charts.
    >
    >Any solutions? Can I design the charts with the RowSource property set and
    >then modify that property in the sub shown above to include a WHERE clause
    >with the [Date] limited?


    If you use SQL explicitly referencing the form it should work:

    strSQL = "... WHERE [Date] BETWEEN [Forms]![YourForm]![txtMinDate] " _
    & "AND [Forms]![YourForm]![txtMaxDate]"


    John W. Vinson[MVP]
     
  5. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Hi Art,

    > I'm using an SQL SELECT statement for the RowSource of the charts.


    Can you base the charts on saved queries? You should be able to add a
    criteria to a saved query that looks to your form to pick up it's parameters.
    It should look something like this in query design view:

    Field: Date
    Table: Your table name

    The following is all on one line:
    Criteria: Between [Forms]![YourFormName]![TxtMinDate] And
    [Forms]![YourFormName]![TxtMaxDate]

    where "YourFormName" is the name of the form that includes TxtMinDate and
    TxtMaxDate.


    Tom

    http://www.access.qbuilt.com/html/expert_contributors.html
    __________________________________________

    "Art Marks" wrote:

    I'm currently opening a report where the date range is limited based on the
    values of 2 textboxes on a form using code like this:

    Private Sub cmdOpenrptWeight_Click()

    DoCmd.OpenReport "rptWeight", acPreview, , "[Date]>=#" & TxtMinDate & "#
    and [Date]<=#" & TxtMaxDate & "#"

    End Sub

    Problem is that the charts at the end of the report aren't affected. I'm
    using an SQL SELECT statement for the RowSource of the charts.

    Any solutions? Can I design the charts with the RowSource property set and
    then modify that property in the sub shown above to include a WHERE clause
    with the [Date] limited?

    Thanks
    --Art
     

Share This Page