Form using options for reports with selected dates

[AussieGal]

Hi,



I have a form with 2 areas on it. One that shows an option group allowing users to select the report the wish to run, the other part shows date fields which the user can select and run the report showing that date range. I currently have 2 buttons which are working to do one thing at a time, one runs reports based on the option choosen, and one runs a report based on dates selected. That part works well. What I am trying to do is have one button to do both. Allow users to select the report on the option buttons, and then select the dates that report should run. I am not sure how to combine the code to have this work. I have put the 2 samples of code I am currently using.

Private Sub cmdRunDates_Click()
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"
    strReport = "rptCountySummary"
    strDateField = "[ServiceDate]"
    lngView = acViewPreview
    'Build the filter string.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    End If
    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    'Open the report.
    DoCmd.OpenReport strReport, lngView, , strWhere
Exit_Handler:
    Exit Sub
Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
End Sub

and

'Private Sub cmdThisMonth_Click()
'If optionRpt = 1 Then
' DoCmd.OpenReport "rptCountySummary", acViewReport
'ElseIf optionRpt = 2 Then
' DoCmd.OpenReport "rptCountyMainRU", acViewReport
'ElseIf optionRpt = 3 Then
' DoCmd.OpenReport "rptCountyInvoice", acViewReport
End If
End Sub

I tried several ways to incorpate this code, but have been unsuccessful so far. I have already wasted 3 days spinning my wheels on this.

I thank anyone in advance who is brave enough to take this one on

[June7]

Seems could just modify cmdRunDates procedure to set the strReport variable based on selection in option group.

Do you want users to have 3 ways to output - the two current options and now a third? Alternatively, set an unbound textbox (can be not visible) to the string constructed by cmdRunDates procedure. Then that string is available for reference in other procedures.

[AussieGal]

What I am trying to get to eventually is to have this form appear that allows users to select the report they want to see, and then select the date range they wish to view. When they click on cmdRunDates it refers to the option box to see what report they want to see, and then filters the date/s the users has entered in the start and end dates. This is primarily what I am working on now. I do eventually want to add 4 command buttons (today, current month, last Month or Year to date) below that would again run the report that is selected in the Option group, yet run the report that shows data based on the button they pushed. Eg. So if they choose the command today 'cmdRunToday' button it will ignore the date range area and instead produce results where service today equals today. That is all over what I am trying to get to, but for now, I am just trying to have the cmdRunDates button look at the option of report they want and then consider the dates selected to run for those days. From your first sentence you seem to be saying exactly what I am trying to do, I just don't know how right the code on how to get there. I have attached a snapshot of my form which should make it easier to explain what I am trying to do.

[June7]

The Date Range provided by user is irrelevant to the 4 period buttons.

Does my suggestion to modify cmdRunDates procedure to set the strReport variable based on selection in option group not address your requirement?

You could have function that selects the report. This function could be called by any of the 5 buttons to get the selected report.

Private Sub cmdThisMonth_Click()
DoCmd.OpenReport GetReport(), acViewReport, , filter criteria here
End If

Private Function GetReport() As String
If optionRpt = 1 Then
GetReport = "rptCountySummary"
ElseIf optionRpt = 2 Then
GetReport = "rptCountyMainRU"
ElseIf optionRpt = 3 Then
GetReport = "rptCountyInvoice"
End If
End Function

In cmdRunDates_Click code:
strReport = GetReport()

[AussieGal]

Hi, Thank you for your help. I guess I am a bit over my head here. I cannot work out where to put the code you placed in my current code to have it work. I think I understand what it is doing, I just can't work out how to make it work.

[AussieGal]

I have attached the code I am trying, but I get that the error of ambiguous name, GetReport. But I thought that is what the Private Function was doing. I am missing something crucial obviously. I am not trying to work on the 4 lower buttons yet, just the Report options with the dates.

Private Function GetReport() As String
If optionRpt = 1 Then
GetReport = "rptCountySummary"
ElseIf optionRpt = 2 Then
GetReport = "rptCountyMainRU"
ElseIf optionRpt = 3 Then
GetReport = "rptCountyInvoice"
End If
End Function


Private Sub cmdRunDates_Click()
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"
strReport = GetReport()
strDateField = "[ServiceDate]"
lngView = acViewPreview
'Build the filter string.
If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If
'Close the report if already open: otherwise it won't filter properly.
If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If
'Open the report.
DoCmd.OpenReport strReport, lngView, , strWhere
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub

[June7]

Place the function procedure in the code module behind the form.


In cmdRunDates_Click instead of

strReport = "rptCountySummary"

use

strReport = GetReport()


I think the revision of cmdThisMonth_Click is clear.


EDIT: I was composing while you posted.

Ambiguous name error usually means there is more than one procedure in the same module with the same name.


BTW, / goes at the front of the ending CODE tag - I fixed that but the indentation was lost.

[AussieGal]

OK. I released I pasted the option group 2 times, and after deleting I got past that ares, now I am getting a run time error of 2467 The expression you entered refers to an object that is closed or doesn't exsist.

I am taken to this line of code:
If CurrentProject.AllReports(strReport).IsLoaded Then

[AussieGal]

Geee, sorry I didn't look at my typing... let me try that again.

OK. I realized I pasted the option group 2 times, and after deleting that I got past that area, now I am getting a run time error of 2467 The expression you entered refers to an object that is closed or doesn't exsist.
I am taken to this line of code:
If CurrentProject.AllReports(strReport).IsLoaded Then

Thank you for your time and patience with me.

[June7]

That line worked before the code edit? Then it should now.

Is the strReport variable getting set?

[AussieGal]

I got it! Thank you so very much. There was something happening for a moment, where the database kept crashing, but it seems to stable again now.

I moved onto to the Today button, where I want users to be able to press on it and it opens the report selected in the Option group. It seems I am having issues working on how to get the where condition to work. I have been trying

Private Sub cmdRunToday_Click()
Me.Visible = False
DoCmd.OpenReport GetReport(), acViewReport, , [ServiceDate] = Date
End Sub

but no luck. I really must thank you for all your help.

[June7]

Need quote marks:

DoCmd.OpenReport GetReport(), acViewReport, , "[ServiceDate] = Date()"

Current month:
DoCmd.OpenReport GetReport(), acViewReport, , "Month([ServiceDate]) = Month(Date())"

Last month:
DoCmd.OpenReport GetReport(), acViewReport, , "Month([ServiceDate]) = Month(DateAdd('m',-1,Date()))"

YTD:
DoCmd.OpenReport GetReport(), acViewReport, , "[ServiceDate] BETWEEN #1/1/" & Year(Date()) & "# AND Date()"

[AussieGal]

Your an absolute legend! That is Australian for so good you will go down in history. Really, thank you so much for helping me out. All of it works perfectly now.

Could I ask one last favor. I played around a bit with examples you gave me, but am not getting anywhere. They now want a last 7 days button. I tried playing around, and the closest thing that looks right to me is DoCmd.OpenReport GetReport(), acViewReport, , "[ServiceDate] Between #(DateAdd("d", -7, Date) & "# AND #" & Date & "#"
It is not working though.

[June7]

Day unit is default when calculating with date so DateAdd function not necessary. Date() function will be recognized so can put inside quotes and not bother with concatenation. Date delimiters not needed because the Date() function defines value as date.

DoCmd.OpenReport GetReport(), acViewReport, , "[ServiceDate] BETWEEN Date()-7 AND Date()"