Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Range for Reports (Access 2002)

    Hello! I created a report based on how well our account
    managers are costing projects, however I don't want the
    report to print out every single entry, I would like it
    to print out either entries within the last 30 days or 7
    days... can this be done?

  2. #2
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Range for Reports (Access 2002)

    I assume there is a date field that is in the record source of the report. You can set the criteria for that to between date() and DateAdd("d", -30, date())
    Obviously for a week earlier you would change the -30 to -7
    I hope this helps

  3. #3
    New Lounger
    Join Date
    Oct 2003
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Range for Reports (Access 2002)

    Zave it does indeed help, however, where do i put that, and what exactly to i type?? (btw: yes i have a date field)

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Date Range for Reports (Access 2002)

    I attach a demo showing two ways to do it.

    Launch the report from a form. On the form put an option group, and in the code behind the command button to open the form, check the value of the option group and set the criteria to one of two values.

    The alternative is to put two text boxes with dtes in them, and set the criteria from the contents of the date boxes.
    Attached Files Attached Files
    Regards
    John



  5. #5
    New Lounger
    Join Date
    Oct 2003
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Range for Reports (Access 2002)

    john that is very very cool. However I do have to say that being somewhat of a newbie to access (only a couple years experience 0 code experience) i have to say i have no idea how you did it! Very very cool though! Any ideas for me?

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Date Range for Reports (Access 2002)

    I am not sure which parts of it your know and don't know, so I will have a go at explaining.

    Do you already have a form with a command button it that launches the report?

    If so , then in design view add an option group, using the option group tool on the toolbar, with the wizard tool activated. Type in the two groups of word you want to use, and let them take values 1 and 2 as the wizard suggest. When it has finished, you will find in the properties that it has been called frame something. rename it to OptDaterange.

    The code behind your command button (if you created it with the wizard, will say:
    (To look at the code, find its onclick event property, click in the words event procedure, then click the three dots that appear.)

    <pre> Dim stDocName As String
    stDocName = "rptProjects"
    DoCmd.OpenReport stDocName, acPreview
    </pre>


    Add into this code the following, before what is already there:
    <pre> Dim strwhere As String
    If Me!OptDaterange = 1 Then
    strwhere = "[ProjectDate] Between #" & Format(Date - 30, "mm/dd/yyyy") & "# and #" _
    & Format(Date, "mm/dd/yyyy") & "#"
    Else
    strwhere = "[ProjectDate] Between #" & Format(Date - 7, "mm/dd/yyyy") & "# and #" _
    & Format(Date, "mm/dd/yyyy") & "#"
    End If
    </pre>

    These two strwhere lines can be each be just one long line. I have broken them to avoid screen scrolling.

    then at the end of the line DoCmd.OpenReport stDocName, acPreview type two commas, then strwhere

    This adds a where condition to the opening report, and acts the same as if there was an extra criteria set for the query that provides data for the report.
    Attached Images Attached Images
    Regards
    John



  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date Range for Reports (Access 2002)

    The easiest way to do it, but then there is no flexibility is to put htis code in the criteria box on the QBE grid of the data source of the report. Then it is always that amount of days records. What John is suggesting is much more flexible.

Posting Permissions

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