Select date range from a form 1

[THWatson]

Using Access 2000

I have a union query called "qunCelebrations" which produces a report called "rptCelebrations." The SQL for the query follows.

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(WeddingAnniversary) 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;

This produces a list of Birthdays, Wedding Anniversaries and Kiwanis Anniversaries for an entire year.

I have set up a form, "frmMonthSelector," which has a list box of Months (value list January through December) and when you select a month from that list box you get the report for that specific month.

The AfterUpdate event for the list box puts the name of the month into a text box called "txtMonth" and the following code is behind a Preview button on the form "frmMonthSelector."

Private Sub cmdPreview_Click()
On Error GoTo Err_cmdPreview_Click

If IsNull(Me.txtMonth) Then
        MsgBox "Please select Month from list box.", vbExclamation
        Me.lstMonths.SetFocus
        Exit Sub
        End If
    
    Dim stDocName As String

    stDocName = "rptCelebrations"
    DoCmd.OpenReport stDocName, acPreview, , "[Sort] = Forms!frmMonthSelector!txtMonth"

Exit_cmdPreview_Click:
    Exit Sub

Err_cmdPreview_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreview_Click
    
End Sub

What I would like to do would be select a range of months and produce a report for, e.g. January through March, or February through June, or whatever.

I can accomplish this by hard-wiring the query, by adding the 3 pieces in Bold print to the query.

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) [b]And Month([DateOfBirth]) Between 2 and 6[/b] 
UNION ALL
SELECT MemberID, LastName, FirstName, WeddingAnniversary, "Wedding Anniversary",Status,DateDiff("yyyy",[WeddingAnniversary],Date())+Int(Format(Date(),"mmdd")<Format([WeddingAnniversary],"mmdd")) AS FixWeddingAnniversaryYears,Month(WeddingAnniversary) 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) [b]And Month([WeddingAnniversary]) Between 2 and 6[/b] 
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) [b]And Month([YearJoined]) Between 2 and 6[/b] 
ORDER BY Month, Day, DateType, LastName, FirstName;

This is terribly inefficient, as it means adjusting the query every time you want to get a range of months.

So, what I would like to do is select a range of months from a form. I have tried various ways to devise this in a form but can't get the syntax behind a command button correct.

Any help would be appreciated.

Thanks.

Tom

 

[PHV]

I'd use 2 combobox with 2 columns (month number, month name)
and then:
DoCmd.OpenReport stDocName, acViewPreview, , "[Month] Between " & Me!cboMonthStart & " And " & Me!cboMonthEnd

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[THWatson]

Perfect!

I had tried something like that. But I had the DoCmd stated as

DoCmd.OpenReport stDocName, acViewPreview, , "[Month] Between " & Forms!frmMonthSelector!cboMonthStart & " And " & Forms!frmMonthSelector!cboMonthEnd

This resulted in a Reserved Error.

Thanks for the correction. Much appreciated.

Tom