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.OpenRepor t.

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

[jsmythe8648]

Previously smithj14

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]

First, try changing the Where Clause From:

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

To:

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

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]

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 (#)).

Point well taken, NeoPa.

[smithj14]

Filter Report by Date Range

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/answers/869045-using-option-group-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]

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

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.

Hence the name "Newbie" under my username.

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.