Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Sep 2006
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    chart showing running time period (Access 2000)

    I created a database which includes amounts of sales and I've been able to create a query which will give me total sales for each month. I was then able to create a chart showing the same information. What I would like to do is to have the chart display the last 12 months only. I want to make it as user friendly as possible, meaning, I want to user to click on one button and the report will pop up. They shouldn't have to enter the beginning and ending dates. Any suggestions?

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

    Re: chart showing running time period (Access 2000)

    You could use code like this in the On Click event of the command button:
    <code>
    Private Sub cmdOpenReport_Click()
    On Error GoTo ErrHandler

    DoCmd.OpenReport ReportName:="rptWhatever", View:=acViewPreview, _
    WhereCondition:="[DateField] >= DateAdd(""yyyy"", -1, Date())"
    Exit Sub

    ErrHandler:
    If Not Err = 2501 Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub
    </code>
    cmdOpenReport is the name of the button.
    rptWhatever is the name of the report to be opened.
    DateField is the name of the field that contains the sales date.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: chart showing running time period (Access 2000)

    So if the -1 in the WhereCondition were changed to -2, would that give the last 2 years?
    Thanks
    chuck

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

    Re: chart showing running time period (Access 2000)

    Yes, indeed. And if you wanted the last 6 months:
    <code>
    ... WhereCondition:="[DateField] >= DateAdd(""m"", -6, Date())"</code>

  5. #5
    New Lounger
    Join Date
    Sep 2006
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: chart showing running time period (Access 2000)

    I truely wish I could understand code. Is that SQL or VBA or just BS? Too bad Microsoft didn't create the Wizards to do more. I will tell my boss that this is out of my realm. It looks as if my question has helped a couple of other people though. Thanks.

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

    Re: chart showing running time period (Access 2000)

    No need to give up yet - it's not that difficult! Try the following:

    - Create a form in design mode.
    - Place a command button on the form (from the Toolbox).
    - Activate the Properties window.
    - Activate the Format tab.
    - Set the Caption property to Open Report or something similar.
    - Activate the Other tab.
    - Set the Name property to cmdOpenReport.
    - Activate the Event tab.
    - Click in the On Click box.
    - Select [Event Procedure] from the dropdown list.
    - Click the button with three dots ... to the right of the dropdown arrow.
    - You'll be taken to the Visual Basic Editor (don't be frightened - it doesn't bite) <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    - Switch to this post in your browser.
    - Select the following code with your mouse:
    <code>
    On Error GoTo ErrHandler

    DoCmd.OpenReport ReportName:="rptWhatever", View:=acViewPreview, _
    WhereCondition:="[DateField] >= DateAdd(""yyyy"", -1, Date())"
    Exit Sub

    ErrHandler:
    If Not Err = 2501 Then
    MsgBox Err.Description, vbExclamation
    End If
    </code>
    - Press Ctrl+C to copy it.
    - Switch back to the Visual Basic Editor.
    - Press Ctrl+V to paste the code into the blank line between Sub cmdOpenReport_Click() and End Sub.
    - Replace rptWhatever with the name of the report you want to open.
    - Replace DateField with the name of the date field on which you want to filter the records.
    - Press Alt+F11 to switch back to Access.
    - Save the form (for example as frmReport).
    - Switch to form view (or close then open the form).
    - Click the button to test it.

  7. #7
    New Lounger
    Join Date
    Sep 2006
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: chart showing running time period (Access 2000)

    What I am trying to do is to make a simple report for a co-worker. He is looking for a "dashboard" report, which will include several charts from one table. Sales by department, sales by month, type of sales by each department etc. I was looking for a way for him to be able to import a spreadsheet, which he gets from the main office, click on one or two buttons from the main Switchboard and then export the report to a Word document or Excel spreadsheet. I know that from the Main Switchboard, I would be able to run macros which would run the report and export it to whichever form I want it to. I was trying to go in the direction of a main query and sub queries. I was thinking that in the query, I could use the criteria box to limit the query to one year prior. I tried >Date(), -365 . At least I was able to create update queries and append queries to manipulate the data that is received. All without needing to know code. I need to think about this more and how to word it properly so other people will understand what I'm trying to do. I appreciate all of your help.

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

    Re: chart showing running time period (Access 2000)

    I thought you were trying to display a report with one chart only. If you want to display several charts, the method I proposed isn't suitable.

    You can certainly create queries that will return only records from the last year. Using
    <code>
    >Date()-365
    </code>
    as criteria for the date field is a good approximation; if you want to take leap years into account, you can use
    <code>
    >DateAdd("yyyy",-1,Date())
    </code>
    instead.

Posting Permissions

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