Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Criteria for a report (Access 2000 all updates)

    I have a report called "rptCelebrations" which provides a list, by month, of Birthdays, Anniversaries, and Club Anniversaries (# of years in the club).

    The record source for this report is a union query called "qunCelebrations." The SQL for the query is
    <code>SELECT MemberID, LastName, FirstName, DateOfBirth as TheDate, "Birthday" as DateType,Status,DateDiff("yyyy",[DateOfBirth],Date())+Int(Format(Date(),"mmdd")<Format([DateOfBirth],"mmdd")) As FixYears,Month(TheDate) As Month,Day([DateOfBirth]) as Day,Switch([Month]=1,"January",[Month]=2,"February",[Month]=3,"March",[Month]=4,"April",[Month]=5,"May",[Month]=6,"June",[Month]=7,"July",[Month]=8,"August",[Month]=9,"September",[Month]=10,"October",[Month]=11,"November",[Month]=12,"December") AS Sort
    FROM tblMembers
    WHERE (tblMembers.Status="Active" Or tblMembers.Status="Senior") And ([DateOfBirth] Is Not Null)
    UNION ALL
    SELECT MemberID, LastName, FirstName, WeddingAnniversary, "Wedding Anniversary",Status,DateDiff("yyyy",[WeddingAnniversary],Date())+Int(Format(Date(),"mmdd")<Format([WeddingAnniversary],"mmdd")) AS FixWeddingAnniversaryYears,Month(WeddingAnniversar y) As Month,Day([WeddingAnniversary]) as Day,Switch([Month]=1,"January",[Month]=2,"February",[Month]=3,"March",[Month]=4,"April",[Month]=5,"May",[Month]=6,"June",[Month]=7,"July",[Month]=8,"August",[Month]=9,"September",[Month]=10,"October",[Month]=11,"November",[Month]=12,"December") AS Sort
    FROM tblMembers
    WHERE (tblMembers.Status="Active" Or tblMembers.Status="Senior") And ([WeddingAnniversary] Is Not Null)
    UNION ALL SELECT MemberID, LastName, FirstName, YearJoined, "Kiwanis Anniversary",Status,DateDiff("yyyy",[YearJoined],DateSerial(Year(Date()),9,30)) AS Sept30KiwanisAnniversary,Month(YearJoined) as Month,Day([YearJoined]) as Day,Switch([Month]=1,"January",[Month]=2,"February",[Month]=3,"March",[Month]=4,"April",[Month]=5,"May",[Month]=6,"June",[Month]=7,"July",[Month]=8,"August",[Month]=9,"September",[Month]=10,"October",[Month]=11,"November",[Month]=12,"December") AS Sort
    FROM tblMembers
    WHERE (tblMembers.Status="Active" Or tblMembers.Status="Senior") And ([YearJoined] Is Not Null)
    ORDER BY Month, Day, DateType, LastName, FirstName;</code>

    (I should add that the "YearJoined" field would be more appropriately named "DateJoined" because it is a complete date, in short form. It was stated thus in the Excel sheet I was given, and from which I started to create the database, and I didn't change it.)

    This produces a report for an entire year, sorted and grouped by month.

    I would like to be able to select a particular month, or a particular date range, and produce the report for only that selected time period.
    I have tried to enter criteria in the query. I have also tried to enter criteria in a form and preview or print from the form.
    But I can't get it to work correctly.

    Any assistance would be greatly appreciated.

    Thanks.

    Tom

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

    Re: Criteria for a report (Access 2000 all updates)

    What if you create a form with text boxes txtStart and txtEnd, and a command button cmdReport with code
    <code>
    Private Sub cmdReport_Click()
    If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
    MsgBox "Please enter both the start and end dates.", vbExclamation
    Exit Sub
    End If
    DoCmd.OpenReport "rptCelebrations", acViewPreview, , "TheDate Between # & _
    Format(Me.txtStart, "mm/dd/yyyy") & "# And #" & _
    Format(Me.txtEnd, "mm/dd/yyyy") & "#"
    End Sub
    </code>
    or a form with a text box txtMonth in which the user enters a month number (1 ... 12) and a command button cmdReport with code
    <code>
    Private Sub cmdReport_Click()
    If IsNull(Me.txtMonth) Then
    MsgBox "Please enter a valid month number.", vbExclamation
    Exit Sub
    End If
    DoCmd.OpenReport "rptCelebrations", acViewPreview, , "[Month] = " & Me.txtMonth
    End Sub</code>

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Criteria for a report (Access 2000 all updates)

    No need to reply.

    I figured out how to add the criteria from a list box selection on a form.

    Thanks.

    Tom

Posting Permissions

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