Choosing daily, weekly, monthly or yearly report based on form control 1

[CantDo]

I have a database where multiple entries are made on a daily basis. On my main form, I have a calendar control (unbound) to allow the user to choose a date - defaulted to current date. There is also an option group (4 options) allowing the user to choose the day, week, month or year related to the date chosen on the calendar control - defaulted to week. Finally, a button calls a procedure to generate a report with the following code:

Private Sub Btn_Report_Click()
On Error GoTo Err_Btn_Report_Click

    Dim strDatePart, stDocName, stLinkCriteria, Criteria1, Criteria2 As String

    Select Case Me.Time_Choice
        Case 1
            strDatePart = """d"""
        Case 2
            strDatePart = """ww"""
        Case 3
            strDatePart = """m"""
        Case 4
            strDatePart = """yyyy"""
    End Select
    
    stDocName = "PeriodicReport"
    Criteria1 = "DatePart(" & strDatePart & ",[Date], 0, 0)"
    Criteria2 = "DatePart(" & strDatePart & ", Forms!Switchboard!CalendarDate, 0, 0)"
    stLinkCriteria = Criteria2 & " = " & Criteria1
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_Btn_Report_Click:
    Exit Sub

Err_Btn_Report_Click:
    MsgBox Err.Description
    Resume Exit_Btn_Report_Click
    
End Sub

However, using DatePart doesn't seem to do what I thought it would; eg., choosing day now gives a report for that day-number in every month of the year and choosing week gives a report for that week-number in every year.

I can't seem to find a solution to this problem, though I've spent a good three days looking.

Can someone suggest another method or help me correct the code I am using? Any help is most appreciated.

Thanks.

 

[WildHare]

Ahh yes, the famous DatePart function rears it's ugly head...

You're almost there. If you think about what you need to do, you will be adding a tiny bit of extra criteria to your criteria guy.

The specific date guy should be easy - your criteria is just the actual date value returned from the calendar control itself.

The week guy is a little trickier - you want, I would imagine, the WEEK number, but only for the current YEAR, yes? In other words, week #37 for 2006, or some such. I'm a little unclear as to how you are passing your criteria statement, but somehow, you'll want to string the current year ( Year(Date()) along with it.

Same thing for the month guy - You don't want ALL MONTH = 10, just MONTH = 10 and YEAR = 2006 guys...

Any clearer now, or have I totally confused the issue???







"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman

 

[PHV]

You may try this:

Private Sub Btn_Report_Click()
Dim strDatePart As String, stLinkCriteria As String
Select Case Me!Time_Choice
Case 1
    strDatePart = "mmdd"
Case 2
    strDatePart = "ww"
Case 3
    strDatePart = "mm"
Case 4
    strDatePart = ""
End Select
stLinkCriteria = "Format([Date],'yyyy" & strDatePart & "')='" _
 & Format(Me!CalendarDate, "yyyy" & strDatePart) & "'"
DoCmd.OpenReport "PeriodicReport", acViewPreview, , stLinkCriteria
Exit Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CantDo]

Thank you PHV! Your solution is simple and works perfectly -- I can't believe I missed it! Thanks again.