Filter Report by Date Range

[smithj14]

I have a form that has an option group (fraReports) which holds a list of reports to print. This part works fine. I select a report name and click print and that report opens.

Now I want to add a text box to enter a service date that will open the selected report using the text box value (service date) to filter the report to only those dates. I have the field [Service_Date] using the format "mmmm-yyyy" on all my forms and reports so it will show May-2009.

Here is the code I am using:

Private Sub cmdPrint_Click()
Dim stWhere As String
Dim stDescrip As String
Dim stDoc As String
Dim stDateField As String
Const stDateFormat = "mmmm-yyyy"
 
stDoc = "Court Placement"
stWhere = "[Service_Date]= " & "'" & Me![txtServiceDate] & "'"
 
If CurrentProject.AllReports(stDoc).IsLoaded Then
    DoCmd.Close acReport, stDoc
End If
 
Select Case [fraReports]
    Case Is = 1
        If IsDate(Me.txtServiceDate) Then
            DoCmd.OpenReport "Court Placement", acViewPreview, stWhere
        Else
            MsgBox "You must enter a valid service date to print this report.", vbInformation, "No Service Date Entered!"
        End If
    Case Is = 2
        If IsDate(Me.txtServiceDate) Then
            DoCmd.OpenReport "Not IV-E Eligible", acViewPreview, stWhere
        Else
            MsgBox "You must enter a valid service date to print this report.", vbInformation, "No Service Date Entered!"
        End If
    Case Is = 3
        If IsDate(Me.txtServiceDate) Then
            DoCmd.OpenReport "Special Adoption Consideration", acViewPreview, stWhere
        Else
            MsgBox "You must enter a valid service date to print this report.", vbInformation, "No Service Date Entered!"
        End If
    Case Is = 4
        If IsDate(Me.txtServiceDate) Then
            DoCmd.OpenReport "Traditional Foster Care", acViewPreview, stWhere
        Else
            MsgBox "You must enter a valid service date to print this report.", vbInformation, "No Service Date Entered!"
        End If
    Case Is = 5
        If IsDate(Me.txtServiceDate) Then
            DoCmd.OpenReport "Treatment Homes", acViewPreview, stWhere
        Else
            MsgBox "You must enter a valid service date to print this report.", vbInformation, "No Service Date Entered!"
        End If
    Case Else
        MsgBox "There is no report selected to print.", vbExclamation, "No Report Selected!"
End Select
End Sub

[NeoPa]

I'm not sure what you actually need help with. Your question doesn't make it clear, and code does not constitute (or replace) a properly asked question.

Literal DateTimes and Their Delimiters (#) may prove of some help, and a suggestion to use the WhereCondition parameter when using DoCmd.OpenReport.

If you need more help, please explain exactly what you need and we'll be pleased to help more directly.

[jsmythe8648]

I had to sign back up i could not do anything on the site.

Basically I have a program to keep track foster home placement. Every month we need to generate multiple reports.

I would like to have the list of reports in an option group. Allowing the user to click which report they want to print.

Then under this option group have a text box to allow input of a service date, so the report selected from the above option group will print for only the service date that was entered in the text box.

As of now I am able to select which report i want to print from the option group and it will open. I just need to add to my code to allow the service date to be filtered. That way when the report opens it will only show the records for that service date. The format i am using for this field[Service_Date] is using the format mmmm-yyyy to allow it to show up like this May-2009 instead of the whole date.

[ADezii]

First, try changing the Where Clause From:

stWhere = "[Service_Date]= " & "'" & Me![txtServiceDate] & "'"

To:

stWhere = "[Service_Date]= " & "#" & Me![txtServiceDate] & "#" 

[NeoPa]

Smithj14 now ok again. Please see HELP! My ACCOUNT is DISABLED! for more details.

[NeoPa]

@ADezii
That will work in America ADezii, but for a large part of the world (Europe etc), and for portable code anywhere, you should really follow the guidance of the link posted earlier (Literal DateTimes and Their Delimiters (#)).

[ADezii]

@NeoPa
Point well taken, NeoPa.

[smithj14]

Ok, I scrapped the idea of using an option group with a date filter for reports. Instead I now have 5 buttons, one button for each report. In the _Click() event of each button I want to use a where clause that filters the report by the date entered into a text box. This text box is on the same form that all the buttons are on.

cmdButton1
cmdButton2
cmdbutton3
cmdButton4
cmdButton5
txtServiceDate

I can filter by date if I use the normal date format, but this date needs to be in the format of: mmmm-yyyy. So it will always show as: Month-Year. I have the format set like this anywhere this [Service_Date] field is used.

My ultimate goal would be for the user to enter a service date in the text box, using the above format, then click the button for the report they need. Then the report would open only showing the records for the specified service date.

So, what I need to know is how can I change my code to recognize this format for the service date?

If you need me to post the code I have let me know.

[OldBirdman]

First, I'm not sure here why command buttons are better than options buttons, because the problem is the same.
I'm not sure what you want your report date range to be. The date mmmm-yyyy (let's use July-2005 for examples here) could mean either the entire month of July05, or 1 July, 2005, or 31 July, 2005. The report might be for the month, the period up to 1 July, 2005, the period up to 31 July, 2005 or the period from either of those July dates until the present, and be either inclusive or exclusive.
But you need a date range, because your format does not allow for a specific day within a month.
Goal here is an Where clause for a range of dates with the syntax:

dteField > dteLow And dteField <= dteHigh

This is NOT A VALID Where clause.

So, for some code for the Where clause, something like:

dteLow = txtService_Date       'First day of month
dteHigh = DateAdd("m", 1, dteLow)    'First day of following month
stWhere = "dteField > #" & dteLow & "# And dteField <= #" & dteHigh & "#"

You're going to need similar code for each command button, or call a common procedure.
Using options buttons seems simplier. The logic, not actual code because I haven't declared any variables, might be:

Private Sub cmdRunReport_Click()
'Check for valid date
If Not IsDate(txtService_Date) Then
    MsgBox "Date is not valid, please try again"
    Exit Sub
End If
'Check for Date not in mmmm-yyyy format
'    You don't want operator to enter something like 7/2/05, 
'            which will pass the IsDate Test
'    This isn't so easy, and is another problem, so no code here, FOR NOW!
 
'Build Report Title
Select Case optGroupName
Case 1
    stReportTitle = "Report 1 - AAA"
Case 2
    stReportTitle = "Report 2 - BBB"
Case 3
    stReportTitle = "Report 3 - CCC"
Case Else
    MsgBox "No Report Selected"
    Exit Sub
End Select
 
'Build Where Clause
dteLow = txtService_Date       'First day of month
dteHigh = DateAdd("m", 1, dteLow)    'First day of following month
stWhere = "dteField > #" & dteLow & "# And dteField <= #" & dteHigh & "#"
 
'Run Report
OpenReport stReportTitle , acViewPreview, stWhere 
End Sub

If you want to actually use individual command buttons, then all the code above belongs in a Sub or Function, and you will pass as a parameter stReportTitle instead of having the Select Case.

[Denburt]

Don't you have several people trying to help you with this issue here at your previous thread?
http://bytes.com/topic/access/answer...up-date-filter

[Denburt]

That looks nice Birdman the only thing I would like to mention is that you can enter mmm-yyyy in the format for any text box and they will see it that way. You should have your tables field set to date and when data is entered make sure they use a consistent day such as Jan-1-2009 or any of the combination Birdman mentioned. This can and will save you a lot of heart ache later on.

[smithj14]

Sorry, yes but I started a new thread because I was taking a different route.
Should I have used the same thread?

Hence the name "Newbie" under my username.

[OldBirdman]

Back in the days of punch-cards to input data, people used to code

If Col4 = "Y" Then
...
Else
...
End If

This logic forgets that someone might get something other than a Y or N into that field. With so many ways to change a textbox, with copy-paste, sendkeys, direct entry, etc. I code for any condition out of habit. The next person to work on this project might change the format without realizing the implications. This mind-set has not hurt me yet

[Denburt]

@smithj14
No apologies are necessary I was just pointing it out. Yes you did change it up a little but the question is still the same and that is how to Filter the report.

@smithj14
Exactly why I didn't ding ya. :)

I'm going to go ahead and merge these though. ok

[smithj14]

Ok, sounds good to me.

Thank both of you I pretty much have it working now, just getting error when there is no data to print. I think it is in the code of the report for "No Data"

Here is the code I used:

I did go back to using the option group as it is much easier, like OldBirdMan stated.

Private Sub cmdPrint_Click()
Dim stWhere As String
Dim stReportName As String
Dim dteLow As String
Dim dteHigh As String
Dim lngView As Long
 
'stDateField = "[Service_Date]"
lngView = acViewPreview
 
 
If Not IsNull(Me.txtServiceDate) Then
    If Not IsDate(Me.txtServiceDate) Then
        MsgBox "The service date entered is not a valid date. Please enter a valid date.", vbOKOnly, "Invalid Service Date!"
        Exit Sub
    End If
Else
    MsgBox "You must enter a service date to continue.", vbOKOnly, "Not Service Date Entered!"
    Exit Sub
End If
 
dteLow = Me.txtServiceDate
dteHigh = DateAdd("m", 1, dteLow)
stWhere = "[Service_Date] > #" & dteLow & "# And [Service_Date] <= #" & dteHigh & "#"
 
Select Case [fraReports]
    Case Is = 1
        DoCmd.OpenReport "Court Placement", lngView, , stWhere
    Case Is = 2
        DoCmd.OpenReport "Not IV-E Eligible", lngView, , stWhere
    Case Is = 3
        DoCmd.OpenReport "Special Adoption Consideration", lngView, , stWhere
    Case Is = 4
        DoCmd.OpenReport "Traditional Foster Care", lngView, , stWhere
    Case Is = 5
        DoCmd.OpenReport "Treatment Homes", lngView, , stWhere
    Case Else
        MsgBox "You have to select a report to print first.", vbOKOnly, "No Report Selected!"
        Exit Sub
End Select
End Sub

Again thanks to all who helped. This is by far the best forum for "Newbie"s like me.

[smithj14]

@Denburt
The user will enter the date in the text box like this: May-2009
Also I have the [Service_Date] field in my table and all forms and reports set to that format so the date always shows this way. Not sure if that is right but it seems to work.

[Denburt]

Cool glad you've got it. Oh and in case your interested...

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "There is no data for the criteria requested!" & vbCrLf & vbCrLf & "Closing report...", vbInformation
    Cancel = True
End Sub

[smithj14]

That is similar to what I have. I am getting a run time error '2501':

The Open Report Action was Canceled.

End Debug

[Denburt]

First I would make sure the report opens with no filter. Then before your select case add a debug statement so you can see the where statement in the immediate pane (in VBA window, hit view then immediate window). I would then manually insert the filter in the filter location and try to run it.

Debug.print strWhere
Select Case [fraReports]

[NeoPa]

@smithj14
There are two very important things to understand here :

1. Using the CODE tags is not optional. Continued such posts is likely to result in problems.
2. Dates are stored internally as numbers. The format of the date is not an attribute that has any importance whatsoever when searching for or processing any date values.
   
   Consider the format you were talking of earlier. mmmm-yyyy.
   Many individual dates can resolve to the same displayed value. All days in a month in fact. A date presented in this format will be interpreted as the first of the month in all cases. What happens when you want to select any dates in May 2009? How can you use anything in this format and expect it to select what you want? It will ignore all entries except those for May 1, 2009.
   
   There is a standard way of handling dates which avoids all these types of inherent problems with dates. You'll find a link to it in posts #2, #6 & #7.
   
   If I say that considering the format of the date you're expecting to see it in, is simply diverting your focus away from the real problem, would that help?

PS. Tags are done as matching pairs where the opening one is surrounded by [...] and the closing one by [/...]. A set of buttons is available for ease of use in the Standard Editor (Not the Basic Editor). The one for the [ CODE ] tags has a hash (#) on it. You can choose which editor to use in your profile options (Look near the bottom of the page).

[smithj14]

@NeoPa
I know it would cause a problem in most cases, but for this, the way the user enters the date is "Month-Year". The placement is for the month regardless of what day of the month it is. If someone is placed on 05/01/2009 and someone is placed on 05/29/2009 they are both with a service date of May-2005. For this purpose we do not use the actual day of the month for anything. There is a number of days field that calculates with a rate field to get totals, but other than that individual days are not needed.

Thank you all for the help.

[smithj14]

I attached a screen shot of the Run Time Error I am getting when a report has no data. This will come up after I get the message box that my code fires when the report has no data. I have made this work before but this time I keep getting the error. I will have these reports print and save as a word doc automatically so if one of the reports is empty i just wanted to cancel it without printing or saving a blank report. The message box comes up fine telling me the report has nothing to print, I click OK then I get the run time error box that wants me to click End or Debug or Help. Is there a way to stop this second message from coming up?

[NeoPa]

It sounds like you've not really grasped what I was saying.

How is the date stored in your understanding? I thought you said it was in a Date/Time field.

If so, I suggest you need to look again at what you think you need. Read my last post carefully.

[smithj14]

@NeoPa
The Service_Date is a Date/Time field. I set the format to mmmm-yyyy for that field in my table. Even if it is stored as a normal date with the mm/dd/yyyy we only use the month and year so the days have no affect on the forms or reports as we use them.

That is why I said it may be the wrong way to do it, but what we need it for it works.

[NeoPa]

Well, if you're still convinced of that, then I can only bid you good luck and adieu.

[smithj14]

Ok, one more problem I am having.
I would like to use the service date filter that you all helped me with, but also filter by a name. The name will come from a combo box that pulls data from the name table.

So basically I need to select a name from the combo box, then enter a service date and have the report open and show all records for only the name selected and by the service date entered.

Right now I can enter the service date and the report opens showing all records for that service date. I just can't figure out how to get my "stWhere" statement to include the name combo box also.