Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open a report for a given month (Access 2000)

    I have a report consiting of 2 fields : customerName and LastUpdated.
    How can i select a month say january or february ?I can build an option group with the 12 months
    but how can i filter for january ? If i know how to open for january, i would easily build the rest of it

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

    Re: Open a report for a given month (Access 2000)

    Do you mean records for (say) January in the current year (2004), or for any January (2004, 2003, ...)?

    You can do this as follows:
    - Create a query based on your table (or on a query you already have)
    - Add two calculated columns:

    YearUpdated: Year([LastUpdated])
    MonthUpdated: Month([LastUpdated])

    - Clear the 'Show' check box for these columns.
    - Enter the following in the Criteria line for YearUpdated if you want records for the current year:

    Year(Date())

    - Omit this if you want records for all years.
    - Enter the following in the Criteria line for MonthUpdated:

    [Forms]![frmSelect]![grpMonths]

    where grpMonths is the name of the option group and frmSelect is the name of the form containing the option group. The option group should have 12 option buttons, labeled January, February, etc., with Option Value 1, 2, etc.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a report for a given month (Access 2000)

    I want to inform you that i applied it sucessfully. It is great what you have taught me !! I think this is a wonderful method of buildinjg
    months reports

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a report for a given month (Access 2000)

    Dear Hans,


    Following your advice i can now open a report for a given month, and it works wonderful :

    DoCmd.OpenReport "rptCalls", acViewPreview

    When however,i want to open the same report but also both for a given date and a given office,i receive no error but the report is blank.:
    DoCmd.OpenReport "rptCalls", acViewPreview, , "Calls.Afid = " & Me.Office
    It doesnt work for me.
    Can you help me?
    you see from the applied db, the afid under question is 5,so the report should show the calls, but it doesnt show it

    I will be very grateful for your help

    regards

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

    Re: Open a report for a given month (Access 2000)

    I'm not sure what you mean by "you see from the applied db". Did you intend to attach a database?

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a report for a given month (Access 2000)

    Yeas, thank you, obviously the db has not been attached.Please apologize me.I am attaching the db.

    Best regards

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open a report for a given month (Access 2000)

    It is working for me. See attachment.
    Francois

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

    Re: Open a report for a given month (Access 2000)

    It works for me too, as for Francois. You may want to add code to prevent the report from opening if there are no data:

    1. Add an On No Data event procedure to the report:

    Private Sub Report_NoData(Cancel As Integer)
    MsgBox "There are no data for this report!", vbInformation
    Cancel = True
    End Sub

    2. Canceling the report will cause error 2501 in the On Click event procedure of the command button on the form. To suppress this, modify the On Click event procedure:

    Private Sub Command0_Click()
    On Error GoTo ErrHandler
    DoCmd.OpenReport "rptCalls", acViewPreview, , "Calls.Afid = " & Me.Office
    Exit Sub

    ErrHandler:
    ' Don't display error 2501 (report canceled)
    If Not (Err = 2501) Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

Posting Permissions

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