Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Unanswered: Dynamically Setting "Control Source"

    As you know, you can dynamically change the content of a text box using now(). And you can format now() to get an integer for Year (e.g. 1949).

    However, I have a crosstab query that generates annual donations to a charity for this year, and the last two years. The relevant fields names are the year (e.g. 2009, 2008, etc).I want to be able to use this query in a report and get for

    this year: 2009, 2008, 2007
    next year: 2010, 2009 and 2008
    following year: 2011. 2010, 2009
    etc.

    I need a way to set the text box "control source" (i.e. the hook to a table or query) dynamically so the reports don't have to be modified every year. Otherwise, we always have the curse of reports based on cross tabs only working for one year.

    Ideas? Is it possible?

    Regards


    John S

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    John

    Have a look at this site Microsoft Access tips: Crosstab query tips

    You can use the Column Headings of a crosstab to specify the headings of a crosstab. In your case it would Year(date);Year(date)-1;Year(date)-2. This is providing the year is a field used for the crosstab.

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    There's also a couple of crosstab report examples in the code bank.


    http://www.dbforums.com/6303905-post54.html

    http://www.dbforums.com/6390529-post84.html
    Last edited by pkstormy; 05-25-09 at 00:21.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1
    Quote Originally Posted by Poppa Smurf
    John

    Have a look at this site Microsoft Access tips: Crosstab query tips

    You can use the Column Headings of a crosstab to specify the headings of a crosstab. In your case it would Year(date);Year(date)-1;Year(date)-2. This is providing the year is a field used for the crosstab.
    Thanks for the ideas. Unfortunately, I am using Access 2000 to develop materials for a small charity using Access 2003 (which creates interesting problems). Given the importance of dynamic cross tab reports in their reporting, its worth getting my head around. Thanks again.

  6. #6
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1
    Thanks for the reference. Unfortunately I am using Access 2000 to develop materials for Access 2004 (very messy). The reference will take a serious effort to get my head around. Thanks again.

  7. #7
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1
    Thanks for the references -- very useful and on point (and reasonably simple). Sub-reports based on the crosstab query looks like an interesting (and simple) option.

    Thanks again.

Posting Permissions

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