Case Select - Report OnOpen - Date Range ? (1 Viewer)

[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

 

[richary]

You're approaching this the wrong way.

In general you have two basic choices:

1. Modify your underlying SQL to reference the paramters in your form
2. use the filter parameter on DoCmd.OpenReport which would be in your button click event.

I see you posted your question to crisleech's question yesterday. He/She used the second method, which I would recommend to you since you have to implement a more flexible WHERE condition than can be easily be written using a query.

I recommend you re-read that post and change your code so that you build your filter and then execute a DoCmd.OpenReport.

Also your code is failing on "Between (MyFromChoose And MyTo)" since this is SQL, not VBA.

 

[misscrf]

You're approaching this the wrong way.

Well, not the response I was expecting, so I am a little taken aback. Thank you for responding.

In general you have two basic choices:

Sounds promising...

1. Modify your underlying SQL to reference the paramters in your form

I dont understand what this means. Can you explain a bit more? What underlying SQL. I am sorry, but while I do my best to construct codes in my apps, I do not really understand the difference between SQL and VB. My report is backed by a query that is in design view. If you mean that I should reference the form text controls from there, I don't see how I can as that will not always be the criteria. The criteria would be specified on the form's second option group, for date. It may be Today, or all dates, 1 date (then 1 of the text boxes) or a range (both text boxes).

2. use the filter parameter on DoCmd.OpenReport which would be in your button click event.

Do you mean put this code in the onclick of the command button for the form? I may want to try this, but I am not sure if I will be missing something by putting it here. (like can I do this, I have 5 different reports, which the button opens based on the first option group, and each of those has a date field on its report.)...Should all the reports' date fields be the same?

I see you posted your question to crisleech's question yesterday. He/She used the second method, which I would recommend to you since you have to implement a more flexible WHERE condition than can be easily be written using a query.

I did try part of his code, but from my research, my understanding was that I cannot use a where statement in a case select statement. Am I wrong?

I recommend you re-read that post and change your code so that you build your filter and then execute a DoCmd.OpenReport.

I will do that. Just for future assistance that you might be able to provide, I thought I would post a pic of my form. The visual may help with any advice you have as to how to pull this together.

There is code in the afterupdate of the first option group to assign the report, and afterupdate of the second to show and hide the date text boxes. The button simply opens the report. The idea is that each report would have the onopen code above (original post - case select statement). That statement is supposed to read the 2nd option group and the text boxes for the report's date criteria.

Thanks.

 

[richary]

Well, not the response I was expecting, so I am a little taken aback. Thank you for responding.

Sorry for sounding rude, it wasn’t meant to be

I dont understand what this means. Can you explain a bit more?

As you mention your report is backed by an underlying query. You can use parameters in this query to reference controls on your form like [Forms]![frmMyFromName]![txtFromDate] to select records to build your form. As it stands, however, I think it might be better for you to use method 2 since it is a great deal more flexible, if a little more code-intensive.

What underlying SQL. I am sorry, but while I do my best to construct codes in my apps, I do not really understand the difference between SQL and VB.

SQL drives the data that is contained in a query, form or report. VB code can be used to control the logic of the application ie what form/report is opened when a button is clicked.

My report is backed by a query that is in design view. If you mean that I should reference the form text controls from there, I don't see how I can as that will not always be the criteria. The criteria would be specified on the form's second option group, for date. It may be Today, or all dates, 1 date (then 1 of the text boxes) or a range (both text boxes).

This is the technique for option 1. This is possible, even with the problems you raise, but I think, looking at the form, it would be confusing to the user to see dates in the From and To boxes that she/he did not put there. As I say, option 2 is the best for you, IMHO.

Do you mean put this code in the onclick of the command button for the form?

Exactly

I may want to try this, but I am not sure if I will be missing something by putting it here. (like can I do this, I have 5 different reports, which the button opens based on the first option group, and each of those has a date field on its report.)...Should all the reports' date fields be the same?

All of this is possible. As I understand your form the user has to select one of a number of reports from a group (which I shall assume is called fraReports) and chose a date or dates controlled by one of 4 options (fraDate). I’ll also assume that in the underlying query the name of the field which has the date that you wish to filter on is called MyDate.

What we will do is build parameters for a DoCmd.OpenReport command using VB which will be in your button’s onClick event


Something like

Dim strWhere
Dim strReport

Select Case fraReports  'choose the report name to open
Case 1 
	StrReportName = “Report1”
Case 2
	strReportName = “Report2”
Case 3
	StrReportName = “rprNoForwardingAddress”
Case 4 
	StrReportName = “rptUpdatedAddress”
Case 5
	StrReportName = “rptCommentsandQuestions”
End select

Select case fraDate 'build the where condition for the report
   Case 1 'today
       strWhere = “MyDate = #” & date() & “#” 'SQL wants hashes around dates
   Case 2  'a particular date
       strWhere = “MyDate =#” & txtFromChoose & “#”
   Case 3 ' date range
       strWhere = “MyDate between #” & txtFromChoose & “# and #” & txtToChoose & “#”
   Case 4 'all dates
       strWhere = “”                            ‘we are selecting all records so no filter needed

 End select

   Docmd.OpenReport strReportName,acViewPreview,,strFilter

Unfortunatly this PC doesn’t have Access installed on it, so I’m guessing somewhat, so please accept my apologies if there are any errors (I expect there will be)

I did try part of his code, but from my research, my understanding was that I cannot use a where statement in a case select statement. Am I wrong?

I’m not sure I understand. You don’t need the WHERE keyword in the where condition of a report, if that’s what you mean??

Hopefully the above makes sense. Any problems please post back and I'll do my best to help.

 

[misscrf]

Excellent. I am going to try this out.

Thank you for all of the explanations. It makes a lot more sense now.

I do have a question about the code:

For the variables that you declared - strwhere and strreport. Does strwhere need to be declared as a date or integer? I was getting an invalid use of null error on all of my choices based on my current case select behind the report. I believe that is because my date variables where declared as strings and I tried to pass dates to them.

Just a thought.

Other than that I have been working toward an answer like this for a while, and I think this is exactly what I am looking for. I will let you know where I get with it.

Thanks!

 

[richary]

No problem, hope it helps you

Re the Dim statements - I forgot to add the type - they should both be string eg

Dim strWhere As String
Dim strReport As String

 

[misscrf]

Hey, I started to try it out, and I got some errors.

Here is what I got from the today code (just by typing it) -

Here is what I got from the date range:

I am not sure how to set this right. I have set these variables as strings and I get that invalid use of null error. This is what I am seeing with them as dates.

 

[richary]

Sorry, my mistake. I typed this in Word and it has converted the quotes to its Smart Quotes (or whatever it calls them)

Make sure that all double quotes and the single quotes for comments are set correctly as in

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

 End Select

 

[misscrf]

WOW YOU RULE!!!!!

It worked! Thank you sooooo much!!!!!

Now I don't have to do that stupid onopen code for every report! This makes sooooo much sense!

Oh, and you were right. The strwhere did need to be declared as a string. Here is the final code, in hopes that it can help others...

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

You have helped me sooooo much!!!!

Thank you.

 

[richary]

You're more than welcome !

 

[misscrf]

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

this part:

[COLOR=Red]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=Red]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.

 

[richary]

Hi

I think this is what you need?

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

DoCmd.OpenReport gstrReportName, acViewPreview, , gstrWhere

End If

richary

 

[misscrf]

but that's what I have.... lol.

The part I am having problems with is the

[COLOR=Red]Me.txtFromChoose.Value < 0 [/COLOR]

I have also tried

[COLOR=Red]Len(Trim$(Me.txtFromChoose & vbNullString)) = 0[/COLOR]

and

[COLOR=Red]isnull (Me.txtFromChoose.Value )[/COLOR]

and

[COLOR=Red]isnull(Me.txtFromChoose)[/COLOR]

and

[COLOR=Red]isnull(Me.txtFromChoose.text)[/COLOR]

and

[COLOR=Red]Me.txtFromChoose.value = ""[/COLOR]

and

[COLOR=Red]Me.txtFromChoose.text = ""[/COLOR]

:-(

Any thoughts on how I am really supposed to catch if someone has not entered anything/a date into a text field that is formatted for date? (It has a date input mask and is formatted for short date.)

 

[richary]

Now that's a bit strange. Isnull should certainly work and tested it just now, if there is no data entered into that field.

Have you tried setting a breakpoint in VB at your "isnull (Me.txtFromChoose.Value )" line and checking the value of txtFromChoose.Value?

 

[misscrf]

It says " Me.txtFromChoose.Value = Null " when I hover over it.

(I put Stop on the line above it, and then tested it. Is that right?)

I also notice that if I hover over the gstrWhere a little bit down...

DoCmd.OpenReport gstrReportName, acViewPreview, , gstrWhere

It says - gstrWhere = "ActivityDate=##"

Could that be the problem?

I thought my if then else was saying that if optgrp = 2 and txt = null then message..

That should mean the docmd.open report is not to happen - ie no strwhere needed?

Does this make sense?

 

[richary]

It says " Me.txtFromChoose.Value = Null " when I hover over it.

(I put Stop on the line above it, and then tested it. Is that right?)

That's correct.

I think I'm going to need see the whole code. txtFromChoose seems to be evaluated correctly. In the meantime, its worth you doublechecking to see that GrpReportDate does, indeed, equal 2 at that line.

The rest of your logic in your post with regard to gstrWhere looks sound to me, so I wouldn't worry about the missing missing date since I assume that (a) it won't have been assigned yet (you would have this validation code before you assign the gstrWhere string?) and (b) it won't get executed anyway, as you point out.

 

[misscrf]

ok here is the entire onclick code. BTW the optiongroup does = 2 when I hover over it...

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

DoCmd.SetWarnings False
If (Forms![frmReports]![GrpReportDate] = 2 And IsNull(Me.txtFromChoose.Value)) Then
Msg = "You must enter a date in the Choose Date field!"
    Style = vbOKOnly
    Title = "Date Must be Entered!"
ElseIf (Forms![frmReports]![GrpReportDate] = 3 And ((Me.txtFromChoose.Value) Or (Me.txtFromChoose.Value))) 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
DoCmd.SetWarnings True
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 else here seems to work. From everywhere I have looked, this code should work.



Me so sad. lol

 

[richary]

Hi

This should do it:

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

DoCmd.SetWarnings False
If (Me.GrpReportDate = 2 And IsNull(Me.txtFromChoose.Value)) Then
    MsgBox "You must enter a date in the Choose Date field!", vbOKOnly, "Date Must be Entered!"
    ElseIf (Me.GrpReportDate = 3 And (IsNull(Me.txtFromChoose) Or (IsNull(Me.txtTo)))) Then
        MsgBox "You must enter a start AND end date!", vbOKOnly, "Dates Must be Entered!"
Else

    DoCmd.OpenReport gstrReportName, acViewPreview, , gstrWhere

End If

DoCmd.SetWarnings True
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

Basically all I did was to add the MsgBox line for the error conditions and changed the second txtFromChoose to txtTo and added the IsNull functions around them.

I tested it and it seems to work as required.

Good luck.

Any other problems, let me know.

 

[misscrf]

Thanks.
I found that the problem with my code was the following:

Msg = "my message text
Style = vbOKOnly
Title = "my message date"
Msgbox Msg, , Title

I was missing the statement to display the message. lol

Thank you for your help.

 

[richary]

Good, I'm glad its all sorted out!