Case Select - Report OnOpen - Date Range ? 1

[misscrf]

Hi, I am having some trouble. I am using a select case with option groups on a form. The first option group (grpReportType) on the form passes a variable to the button on the form (cmdOpenReport - onclick), to choose what report to open.

In the report open code , I am trying this Case Select code to look at the second option group on the form (grpReportDate). This should tell the report what date option to choose.
1 is today (me.reportdatefield = Date)
2 is choose a date (form date field)
3 is date range, and
4 is all.

It is 3, the date range, that I cannot seem to get right.

Here is my latest attempt:

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open

Dim MyFromChoose As Date
Dim MyTo As Date
MyFromChoose = [Forms]![frmReports]![txtFromChoose]
MyTo = [Forms]![frmReports]![txtTo]

Select Case Forms![frmReports]![GrpReportDate]
Case 1
Me.ActivityDate = Date

Case 2
Me.ActivityDate = MyFromChoose

Case 3

Me.ActivityDate Between(MyFromChoose And MyTo)
  
Case 4
Me.ActivityDate = "*"

End Select

Exit_Report_Open:
    Exit Sub

Err_Report_Open:
    MsgBox Err.Description
    Resume Exit_Report_Open
End Sub

The most common error I get is:
" Compile Error
Expected: Line or number or label or statement or end of statement"

Here are some of the other statements that I have tried for Case 3. None of them have worked either.

Me.ActivityDate  >=  Format(Forms![frmReports]![txtFromChoose], "mm/dd/yyyy") And <= Format(Forms![frmReports]![txtTo], "mm/dd/yyyy") 
-------------


Me.ActivityDate & " Between " & Format(Forms![frmReports]![txtFromChoose], "\#mm\/dd\/yyyy\#") _
                & " And " & Format(Format(Forms![frmReports]![txtTo], "\#mm\/dd\/yyyy\#")
----------------------

"Me.ActivityDate Between #" & Format(Forms![frmReports]![txtFromChoose],"mm/dd/yyyy") & "# And #" & Format(Forms![frmReports]![txtTo],"mm/dd/yyyy") & "#"
------------------


((Me.ActivityDate) between DateValue('" & 
Forms![frmReports]![txtFromChoose] & "') AND DateValue('" & 
Forms![frmReports]![txtTo] & "'));"
----------------

"Me.ActivityDate >=#" & Forms![frmReports]![txtFromChoose] & "# AND Me.ActivityDate <= #" & Forms![frmReports]![txtTo] 
& "#" 
--------------------------

"Me.ActivityDate >= #" & Forms![frmReports]![txtFromChoose] & "# AND Me.ActivityDate <= #" & Forms![frmReports]![txtTo] & "#"
--------------------

Me.ActivityDate Between & Format(Forms![frmReports]![txtFromChoose], "mm/dd/yyyy") & "# And #" & Format(Forms![frmReports]![txtTo], "mm/dd/yyyy") & "#"
--------------------

((tblActivities.ActivityDate) Between [Forms]![frmReports]![txtFromChoose] And [Forms]![frmReports]![txtTo])

At least I can say that I am trying. At most I can say that I cannot seem to solve this and I would really like to.

Any help is appreciated!

Thanks

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[THWatson]

misscrf
In Case 3, have you tried removing the brackets.

Change
Me.ActivityDate Between(MyFromChoose And MyTo)

To
Me.ActivityDate Between MyFromChoose And MyTo

With the brackets in Access is reading "MyFromChoose And MyTo" as one part and is looking for the other part to complete the Between clause.

Tom

 

[misscrf]

Thanks for the suggestion.

Without the brackets, it is red, and says "expected end of statement" highlighting MYFromChoose.

With the brackets, it seems to take it, making the between and AND blue. When I run it, it says "sub or function not defined" highlighting the between. That at least confirms your suspisions about the brackets cutting off the statement.

Back to the drawing board!!!

Keep the suggestions coming!


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[THWatson]

misscrf
Is ActivityDate a field from your table or query?

Since things are running properly on all but Case 3, I'm wondering if the problem lies with Access trying to process Me.ActivityDate with a Between clause. The other cases are all single dates.

If ActivityDate is a Date field from your table or query upon which the report is based, you could try...

"[ActivityDate] Between MyFromChoose And MyToChoose"

You didn't post the part of the code that opens the report but I imagine the cases form the Where part of the DoCmd line.

Tom

 

[misscrf]

Thanks for the response, Tom.

That gave me a compile error.

I did find that the today case ( case 1 ) is not working either. It don't think that any of them are, they are just not giving me errors. 4 works (all dates). But 1 (today) gives me all dates too.

Here is what I can show you.

The pic of the form, for choosing the report and date criteria:

Declaration at top of frmReport form code:

Dim gstrReportName As String

The code behind the first option group on that form:

Private Sub GrpReportType_AfterUpdate()
On Error GoTo Err_GrpReportType_AfterUpdate

Select Case Forms![frmReports]![GrpReportType]
Case 1
gstrReportName = "rptName1"

Case 2
gstrReportName = "rptName2"

Case 3
gstrReportName = "rptNoForwardingAddress"

Case 4
gstrReportName = "rptUpdatedAddress"

Case 5
gstrReportName = "rptCommentsQuestions"

End Select

Exit_GrpReportType_AfterUpdate:
    Exit Sub

Err_GrpReportType_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_GrpReportType_AfterUpdate
End Sub

The code behind the 2nd option group:

Private Sub GrpReportDate_AfterUpdate()
On Error GoTo Err_GrpReportDate_AfterUpdate


If GrpReportDate = 1 Or GrpReportDate = 4 Then
Me.txtFromChoose.Visible = False
Me.txtTo.Visible = False
ElseIf GrpReportDate = 2 Then
Me.txtFromChoose.Visible = True
Me.lblFromTo.Caption = "Enter a Date"
Me.txtTo.Visible = False
ElseIf GrpReportDate = 3 Then
Me.txtFromChoose.Visible = True
Me.lblFromTo.Caption = "From"
Me.txtTo.Visible = True
End If

Exit_GrpReportDate_AfterUpdate:
    Exit Sub

Err_GrpReportDate_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_GrpReportDate_AfterUpdate
End Sub

The code behind the command button:

Private Sub cmdOpenReport_Click()
On Error GoTo ErrorHandler
Dim Msg, Style, Title

If IsNull(GrpReportType) Then
    Msg = "You must choose what report you would like to view!"
    Style = vbOKOnly
    Title = "Report Must Be Choosen!"
ElseIf GrpReportDate = 2 And IsNull(Me.txtFromChoose) Then
Msg = "You must enter a date in the Choose Date field!"
    Style = vbOKOnly
    Title = "Date Must be Entered!"
ElseIf GrpReportDate = 3 And (IsNull(Me.txtFromChoose) Or IsNull(Me.txtTo)) Then
Msg = "You must enter a start AND end date!"
    Style = vbOKOnly
    Title = "Dates Must be Entered!"
Else
DoCmd.OpenReport gstrReportName, acViewPreview, , , acWindowNormal
End If
ExitHandler:
    Exit Sub

ErrorHandler:
If Err = 2501 Then
    Resume ExitHandler
    
    Else
        MsgBox Err.Description
        Resume ExitHandler
        
        End If
'    msgbox Err.Description
'    Resume Exit_cmdPreviewRpt_Click
End Sub

Latest code behind the NoForwardingAddress report (getting this one right, then replicating to others)

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open

Dim MyFromChoose As Date
Dim MyTo As Date
MyFromChoose = [Forms]![frmReports]![txtFromChoose]
MyTo = [Forms]![frmReports]![txtTo]

Select Case Forms![frmReports]![GrpReportDate]

Case 1
Me.ActivityDate = Date

Case 2
Me.ActivityDate = MyFromChoose

Case 3
Me.ActivityDate = "Between " & MyFromChoose & " And " & MyTo

Case 4
Me.ActivityDate = "*"

End Select

Exit_Report_Open:
    Exit Sub

Err_Report_Open:
    MsgBox Err.Description
    Resume Exit_Report_Open
End Sub

Any help would be great!


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[THWatson]

misscrf
In the command button cmdOpenReport, I don't see any Where clause in the DoCmd line...
DoCmd.OpenReport gstrReportName, acViewPreview, , , acWindowNormal

You would need a Where clause that indicates the date(s) selected in the option group. Let's just look at one for today's date.
Something such as...

DoCmd.OpenReport gstrReportName, acViewPreview,,"[YourDateField] = Date()"

Secondly, in the I think I would be inclined to try including in each Case statement what is supposed to happen if that case is selected. Something such as...
Case 1
DoCmd.OpenReport gstrReportName, acViewPreview,,"[YourDateField] = Date()"
Case 2
DoCmd.OpenReport gstrReportName, acViewPreview,,"[YourDateField] = MyFromChoose"
etc.

I'm still not clear what ActivityDate is. That's why I indicated [YourDateField]

Tom

 

[misscrf]

OK, I think we are coming closer to being on the same page. The case select statement is currently on the OnOpen code of the NoforwardingAddress report. the ActivityDate is a field on that report from it's underlying query (ie it is the query date field).

There is no where statement in the docmd.openreport because the where is the case select statement in the report's onopen.

I wasn't sure how to contruct a where in the onclick docmd code that would be like a case select as the one I am struggling with.

Part of that is because I have 5 reports. If I were to put a where clause that what a where depending on the 2nd option group, wouldn't all 5 reports have to have the same name for their date field?

If I am getting this wrong, please let me know!

Thanks.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[THWatson]

misscrf
This gets awfully circular, doesn't it?

So, let's see if this is in the correct order...
1. Choose a Report from that Option Group
2. Enter dates in the From and To boxes
3. Choose a Date option from GrpReportDate Option Group
4. On the Report's Open event, the code looks at the From and To boxes, depending upon which option has been selected in the GrpReportDate

I'm also assuming that the Report's RecordSource is a query in which ActivityDate is one of the fields.

So here's the question:
What is changing the Report's RecordSource, dependent upon what date options are chosen?

Might you have to
1. Leave the RecordSource blank, to be filled by code
2. Add in the code on the Report's Open event an SQL statement that changes dependent upon the date option. You could build the SQL statement in lines, with the last line being strWhere, and the strWhere line would be the only one that needs to change.

Something such as...
Dim strSQL as string
Dim strWhere as string
strSQL = "SELECT etc. "

The strSQL and the strWhere clause would be put in each of the case statements.
Case 1
strSQL = strSQL
strWhere = "[ActivityDate] = Date()"
Case 2
strSQL = strSQL
strWhere = "[ActivityDate] = MyFromChoose"
etc.

I think what I might do is get one of them working correctly, and then you will know where to go from there.

Tom

 

[misscrf]

OK, to start, thanks for responding.

The order at the top of your reply is close. Here is the correct order:

1. Choose a Report from that Option Group
2. Choose a Date option from GrpReportDate Option Group
3. If you choose an option to enter 1 date or a date range, then enter dates in the From and To boxes
4. On the Report's Open event, the code looks at the 2nd Option group and possibly the From and To boxes.

The hard part is that I have 5 reports. So they each have their own "ActivityDate" field in their underlying query, and txtActivityDate control on the report. (This database is normalized so I am pulling different activities, but they all get the same main data to work with)

I have been working on some of this stuff and have tried to abandon the OnOpen for the report, putting that code into a where statement for the form's onclick event.

This is what I have, but I am getting errors:

Private Sub cmdOpenReport_Click()
On Error GoTo ErrorHandler
Dim Msg, Style, Title

Select Case Forms![frmReports]![GrpReportType]
Case 1
gstrReportName = "rptExclusions"

Case 2
gstrReportName = "rptObjections"

Case 3
gstrReportName = "rptNoForwardingAddress"

Case 4
gstrReportName = "rptUpdatedAddress"

Case 5
gstrReportName = "rptCommentsQuestions"

End Select

Select Case Forms![frmReports]![GrpReportDate]

[COLOR=#ff0000]Case 1 
gstrWhere = “ txtActivityDate = #” & date() & “#”

Case 2
strWhere = “MyDate =#” & txtFromChoose & “#”

Case 3
gstrWhere = “txtActivityDate between #” & txtFromChoose & “# and #” & txtToChoose & “# ”[/color]

Case 4
gstrWhere = ""

End Select

If GrpReportDate = 2 And Me.txtFromChoose.Value < 0 Then
Msg = "You must enter a date in the Choose Date field!"
    Style = vbOKOnly
    Title = "Date Must be Entered!"
ElseIf GrpReportDate = 3 And (Me.txtFromChoose.Value < 0 Or Me.txtTo.Value < 0) Then
Msg = "You must enter a start AND end date!"
    Style = vbOKOnly
    Title = "Dates Must be Entered!"
Else

DoCmd.OpenReport strReportName, acViewPreview, , gstrWhere

End If
ExitHandler:
    Exit Sub

ErrorHandler:
If Err = 2501 Then
    Resume ExitHandler
    
    Else
        MsgBox Err.Description
        Resume ExitHandler
        
        End If
'    msgbox Err.Description
'    Resume Exit_cmdPreviewRpt_Click
End Sub

Here is the error I get for the today code:

I have substituted txtActivityDate (the report control) and ActivityDate (the report's query field) and still get this error. It doesn't seem to be that.

Here is the error I get over the range:

Maybe this code is in the right path. Any thoughts/suggestions would be great.

Is this a good way to go? Is there a better way? Can you/anyone help with fixing the gstrwhere's?

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[THWatson]

misscrf
Since you are abandoning using the report's Open event, I won't bother with the clauses in it, but will try and show the problems I see with the lines in the form, and I will put my responses in blue.

First, though, a question: Is MyDate a field in the table or query? If not, you need to reference a field that is in the table or query that populates the report. So, if MyDate is not a field, you need to replace it with a field, such as the ActivityDate you showed before.

gstrWhere = "MyDate = #" & Date() & "#"
gstrWhere = "[MyDate] = Date()"

strWhere = "MyDate Between #" & txtFromChoose & "# and #" & txtToChoose & "#"
strWhere = "[MyDate] Between Forms!YourFormName!txtFromChoose And Forms!YourFormName!txtToChoose"

If this isn't getting you anywhere, and you are interested in sending the database to me (without any sensitive data, but with a few records to work with) I could have a look.

Tom

 

[misscrf]

Thank you so much for all of your help. I actually got the code working now. For your benefit, and anyone else looking for this type of code, here is the final code. I now have all of my code behind the command button on the report form, which opens the report (which ever is chosen) with the correct date criteria (which ever is chosen).

Private Sub cmdOpenReport_Click()
On Error GoTo ErrorHandler
Dim Msg, Style, Title
Dim gstrReportName As String
Dim gstrWhere As String

Select Case Forms![frmReports]![GrpReportType]
Case 1
gstrReportName = "rptExclusions"

Case 2
gstrReportName = "rptObjections"

Case 3
gstrReportName = "rptNoForwardingAddress"

Case 4
gstrReportName = "rptUpdatedAddress"

Case 5
gstrReportName = "rptCommentsQuestions"

End Select

Select Case Forms![frmReports]![GrpReportDate]

   Case 1 'today
       gstrWhere = "ActivityDate = #" & Date & "#"   'SQL wants hashes around dates
       
   Case 2  'a particular date
       gstrWhere = "ActivityDate =#" & Me.txtFromChoose & "#"
       
   Case 3 ' date range
       gstrWhere = "ActivityDate between #" & Me.txtFromChoose & "# and #" & Me.txtTo & "#"
       
   Case 4 'all dates
      gstrWhere = ""                            'we are selecting all records so no filter needed

End Select


If GrpReportDate = 2 And Me.txtFromChoose.Value < 0 Then
Msg = "You must enter a date in the Choose Date field!"
    Style = vbOKOnly
    Title = "Date Must be Entered!"
ElseIf GrpReportDate = 3 And (Me.txtFromChoose.Value < 0 Or Me.txtTo.Value < 0) Then
Msg = "You must enter a start AND end date!"
    Style = vbOKOnly
    Title = "Dates Must be Entered!"
Else

DoCmd.OpenReport gstrReportName, acViewPreview, , gstrWhere

End If
ExitHandler:
    Exit Sub

ErrorHandler:
If Err = 2501 Then
    Resume ExitHandler
    
    Else
        MsgBox Err.Description
        Resume ExitHandler
        
        End If
'    msgbox Err.Description
'    Resume Exit_cmdPreviewRpt_Click
End Sub

Everything works great, and all reports now react correctly from this menu.

Thanks a bunch for all of your help. It is always helpful to have someone respond and be willing to at least talk through what you are doing. It helps you to get to the meat of the issue.

Gotta love this forum and it's posters!


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[THWatson]

misscrf
Great! You have it working!!

Tom

 

[misscrf]

well.....
I spoke too soon. The main of that code is working, but the last part is having a problem.

this part:

[COLOR=#ff0000]If GrpReportDate = 2 And Me.txtFromChoose.Value < 0 Then[/color]
Msg = "You must enter a date in the Choose Date field!"
    Style = vbOKOnly
    Title = "Date Must be Entered!"
[COLOR=#ff0000]ElseIf GrpReportDate = 3 And (Me.txtFromChoose.Value < 0 Or Me.txtTo.Value < 0) Then[/color]
Msg = "You must enter a start AND end date!"
    Style = vbOKOnly
    Title = "Dates Must be Entered!"
Else

DoCmd.OpenReport gstrReportName, acViewPreview, , gstrWhere

End If

The red parts are the problem. If the second option group is 2 and there is nothing in the from textbox, then I need a message box. If the second option group is 3 and there is nothing in the from textbox or the to textbox, then I need a message box.

The issue seems to be with how to say "TEXTBOX = Null"
I know that is wrong, but I have tried many variations, and can't seem to get it.

If you need errors, let me know.

Thanks.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[THWatson]

misscrf
First of all, let's ask the question: How could the value be less than 0 (Me.txtFromChoose.Value < 0)? Wouldn't that would mean that a 0 entry would work?

What you are trying to catch is the user not making an entry. So, in the first situation try this...
If GrpReportDate = 2 And IsNull(Me.txtFromChoose) Then

In the ElseIf situation, try this...
ElseIf GrpReportDate = 3 And IsNull(Me.txtFromChoose) Or IsNull(Me.txtTo) Then

Those should work.

If the second one doesn't work, try adding brackets around the two parts with the OR operator...
ElseIf GrpReportDate = 3 And (IsNull(Me.txtFromChoose) Or IsNull(Me.txtTo)) Then


Tom

 

[misscrf]

I have tried that before I got to this, and it didn't work. I can run them and let you know what errors I get for which in the morning... if that will help.

I have tried many ways to do this part as well. lol.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[THWatson]

misscrf
I won't be around tomorrow morning, but I will check in here tomorrow evening and see how you made out. You can let me know.

Tom