Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128

    Question Connect Two Charts to Start & End date, and pull up Field Values from End date?


    I have two issues:

    1) I have two charts that need to display information for same start date and end date. I can get it so that the criteria of the query prompts the user, but then it has to occur twice (once for each chart). I can just have the report query prompt once for a start date and end date; but then the charts use the data from all dates, and the report creates the same report for all the days from start date to end date...

    How do I prompt the user for a start date and end date only once, but get that date to feed into both charts?

    2) I need to also use the end date to populate some field values. Essentially, I have some general information that needs to correspond to the end date inputted.

    How do I connect field values only to the end date from when the user is prompted?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,324
    Use a form for user input. Query parameters refer to the controls on form for criteria. With this technique the form must remain open when report runs.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    I've seen this technique was hoping to avoid it if possible. Is there another way I can do this?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,324
    Not that I know of.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    I guess I have to bit the bullet.

    How do I connect the dates to the chart tables by using an input form?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,324
    Reference the controls on form as parameter inputs.

    Criteria under date field: Between Forms!formname.Start And Forms!formname.End
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    Thank you, I actually just figured this out, but as always, you are a great help June7!

  8. #8
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    June7, one last question.

    I am now using an input form and it works as needed for the two charts.

    I still need to connect the field values to the [End Date].

    I have tried the following in the query Criteria of the field values:

    <=[Forms]![Form1]![End Date]

    =[Forms]![Form1]![End Date]

    [Forms]![Form1]![End Date]

    [End Date]


    Every time I run the form with the Start Date and End Date, I get the correct chart values, but each field value is blank. What do I need to do so that the field values only use the End Date?

    Thank you!

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,324
    Don't understand the issue.

    There is one date field that needs filter criteria?

    There are two boxes on form for input of start and end dates?

    The criteria in query is the expression I suggested?

    Don't understand 'each field value is blank' and 'only use the End Date'.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    Hi June7,

    The charts need to have both the [Start Date] and [End Date] so that they are properly filled out.

    However, the field values, such as Name, Company, etc., need to use only the [End Date] field value.

    If I leave it, then I get reports for each day with the same chart information, but I need one report with the [End Date] field values matter for the basic information.

    Example:

    I choose a range of dates from 4/1/2012 to 4/3/2012 on my user input form and click the button to open my report.

    The chart on the report uses both 4/1/2012 through 4/3/2012 to plot the chart.

    The fields Name and Company use only 4/3/2012 to fill in the field values.

    Only one report is created.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,324
    You want chart to include data for a date range but report show records for only one day?

    Want to provide project for analysis?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    Here is the project with the table, user input form for dates, and the report with the charts.

    Thanks June7!
    Attached Files Attached Files

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,324
    So the charts are based on a start/end range but the records shown will be all records prior to the end date.

    If you move the charts to the Report header and reduce size of the Detail section do you get the desired output? Add a graphic line in Detail section to define each record.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    The charts will be based on the start/end range, the records shown will be ONLY the records for the end date.

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,324
    The criteria you have for the report RecordSource query is <= the end date. That will retrieve all records dated prior and equal to the end date. If you want records for only the one day must be = only.

    Did you try moving the charts as suggested? Or if there will be only one record for each date, moving the charts should not be needed. Just fix the criteria for the report.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Start and End Date trim.
    By jon6035 in forum Queries
    Replies: 7
    Last Post: 09-28-2011, 05:07 PM
  2. Start & End Date Query
    By batowl in forum Queries
    Replies: 1
    Last Post: 05-16-2011, 09:11 AM
  3. Replies: 5
    Last Post: 02-06-2011, 04:32 PM
  4. Replies: 1
    Last Post: 07-07-2010, 04:22 PM
  5. Replies: 1
    Last Post: 12-09-2005, 10:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums