How can I create weekly reports?? 2

[rvargas79]

Hi,

I am a beginner in Access, and I need some help on creating weekly reports.
I created a database to track events and attendees for the event.
I want to be able to print weekly reports for the events and for the attendees. both can be independent, example if have 3 events in a week i want to know how many attendees where there in the 3 events.
I want to create a form with buttons for:
weekly reports, quarterly reports, and yearly reports.
when users click on a button ask for dates and then preview and print the report.

Can anyone help me?
thank you

 

[ReVeLaTeD]

If the dates are stored in your table, you can accomplish this very easily with a Simple Query. Design the report layout first, then make enough opies of it.

If you create the first query with all of the information from the table that you want to see on the report, you're about 75% finished. That query just needs to allow the user to type in which date range they want to view. Do this by putting this as the criteria for the Date field in the query:

Between [Enter the first date:] And [Enter the end date:]

This will prompt the user to put in the date range they want to view. Save this query as a name you'll remember, like "Choose Dates" or something, and set it as the source of the 1st report.

Copy that query, and then on the new copy, change the Criteria on the Date field thusly:

Between 1/1/2004 And 12/1/2004

That's your yearly one. Make that the source of the 2nd report.

With those two queries, just lather/rinse/repeat until you have queries set up for each date range you want to do. You're just changing the Date Criteria each time to match what you want to view, and then tying that query to a report.

As a small tip, if you want to lessen the amount of overall maintenance you need to do, you may want to just do the first query, tie it to a report, and use that one exclusively, because then you won't have to go back and change anything for next year.

Hope that helps and is not too confusing.

 

[dhookom]

I would never create two copies of the same report if all I wanted to do was filter their records differently. One report with either parameters or references to controls on forms in the criteria should be enough.

I often create a form with two text boxes for beginning and ending dates. I add a list box (lboDates) with a row source type of "Value list" and Row Source of:
[blue]"Clear";"This Yr";"This Qtr";"This Mth";"This Wk";"Last Yr";"Last Qtr";"Last Mth";"Last Wk"[/blue]
I have code in the after update event of the list box of :

Private Sub lboDates_AfterUpdate()
  '============================================================
  '     Purpose:
  '   Copyright: Copyright 2003
  '  Programmer: Duane Hookom
  ' Called From:
  '        Date: 3/31/2003
  '  Parameters:
  '============================================================
    On Error GoTo lboDates_AfterUpdate_Err
    Dim strErrMsg As String 'For Error Handling

    Select Case Me.lboDates
        Case "Clear"
            Me.txtFromDate = Null
            Me.txtToDate = Null
        Case "This Yr"
            Me.txtFromDate = DateSerial(Year(Date), 1, 1)
            Me.txtToDate = DateSerial(Year(Date), 12, 31)
        Case "This Qtr"
            Me.txtFromDate = GetQtrStart(Date)
            Me.txtToDate = GetQtrEnd(Date)
        Case "This Mth"
            Me.txtFromDate = DateSerial(Year(Date), Month(Date), 1)
            Me.txtToDate = DateSerial(Year(Date), Month(Date) + 1, 0)
        Case "This Wk"
            Me.txtFromDate = DateAdd("d", -WeekDay(Date) + 1, Date)
            Me.txtToDate = DateAdd("d", 6, Me.txtFromDate)
        Case "Last Yr"
            Me.txtFromDate = DateSerial(Year(Date) - 1, 1, 1)
            Me.txtToDate = DateSerial(Year(Date) - 1, 12, 31)
        Case "Last Qtr"
            Me.txtFromDate = GetQtrStart(DateAdd("m", -3, Date))
            Me.txtToDate = GetQtrEnd(DateAdd("m", -3, Date))
        Case "Last Mth"
            Me.txtFromDate = DateSerial(Year(Date), Month(Date) - 1, 1)
            Me.txtToDate = DateSerial(Year(Date), Month(Date), 0)
        Case "Last Wk"
            Me.txtFromDate = DateAdd("d", -WeekDay(Date) + 1, Date) - 7
            Me.txtToDate = DateAdd("d", 6, Me.txtFromDate)

    End Select
            

lboDates_AfterUpdate_Exit:
    On Error Resume Next
    Exit Sub

lboDates_AfterUpdate_Err:
    Select Case Err
        Case Else
            strErrMsg = strErrMsg & "Error #: " & Format$(Err.Number) & vbCrLf
            strErrMsg = strErrMsg & "Error Description: " & Err.Description
            MsgBox strErrMsg, vbInformation, "lboDates_AfterUpdate"
            Resume lboDates_AfterUpdate_Exit
    End Select
End Sub

I use the values in the text boxes in a where clause of DoCmd.OpenReport. You could also set the criteria in your report's record source to something like:
Between Forms!frmRptSlct!txtFromDate And Forms!frmRptSlct!txtToDate


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ReVeLaTeD]

Duane, that's very interesting code. And so long as there were comment tags all over the place within it, I would recommend it over my solution, but as is, it might be daunting to some.