By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,220 Members | 1,372 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,220 IT Pros & Developers. It's quick & easy.

Filter Report by Date Range

P: 18
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdPrint_Click()
  2. Dim stWhere As String
  3. Dim stDescrip As String
  4. Dim stDoc As String
  5. Dim stDateField As String
  6. Const stDateFormat = "mmmm-yyyy"
  8. stDoc = "Court Placement"
  9. stWhere = "[Service_Date]= " & "'" & Me![txtServiceDate] & "'"
  11. If CurrentProject.AllReports(stDoc).IsLoaded Then
  12.     DoCmd.Close acReport, stDoc
  13. End If
  15. Select Case [fraReports]
  16.     Case Is = 1
  17.         If IsDate(Me.txtServiceDate) Then
  18.             DoCmd.OpenReport "Court Placement", acViewPreview, stWhere
  19.         Else
  20.             MsgBox "You must enter a valid service date to print this report.", vbInformation, "No Service Date Entered!"
  21.         End If
  22.     Case Is = 2
  23.         If IsDate(Me.txtServiceDate) Then
  24.             DoCmd.OpenReport "Not IV-E Eligible", acViewPreview, stWhere
  25.         Else
  26.             MsgBox "You must enter a valid service date to print this report.", vbInformation, "No Service Date Entered!"
  27.         End If
  28.     Case Is = 3
  29.         If IsDate(Me.txtServiceDate) Then
  30.             DoCmd.OpenReport "Special Adoption Consideration", acViewPreview, stWhere
  31.         Else
  32.             MsgBox "You must enter a valid service date to print this report.", vbInformation, "No Service Date Entered!"
  33.         End If
  34.     Case Is = 4
  35.         If IsDate(Me.txtServiceDate) Then
  36.             DoCmd.OpenReport "Traditional Foster Care", acViewPreview, stWhere
  37.         Else
  38.             MsgBox "You must enter a valid service date to print this report.", vbInformation, "No Service Date Entered!"
  39.         End If
  40.     Case Is = 5
  41.         If IsDate(Me.txtServiceDate) Then
  42.             DoCmd.OpenReport "Treatment Homes", acViewPreview, stWhere
  43.         Else
  44.             MsgBox "You must enter a valid service date to print this report.", vbInformation, "No Service Date Entered!"
  45.         End If
  46.     Case Else
  47.         MsgBox "There is no report selected to print.", vbExclamation, "No Report Selected!"
  48. End Select
  49. End Sub
May 6 '09 #1
Share this Question
Share on Google+
25 Replies

Expert Mod 15k+
P: 31,707
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.
May 6 '09 #2

P: 1
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.
May 6 '09 #3

Expert 5K+
P: 8,679
First, try changing the Where Clause From:
Expand|Select|Wrap|Line Numbers
  1. stWhere = "[Service_Date]= " & "'" & Me![txtServiceDate] & "'"
Expand|Select|Wrap|Line Numbers
  1. stWhere = "[Service_Date]= " & "#" & Me![txtServiceDate] & "#" 
May 6 '09 #4

Expert Mod 15k+
P: 31,707
Smithj14 now ok again. Please see HELP! My ACCOUNT is DISABLED! for more details.
May 7 '09 #5

Expert Mod 15k+
P: 31,707
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 (#)).
May 7 '09 #6

Expert 5K+
P: 8,679
Point well taken, NeoPa.
May 7 '09 #7

P: 18
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.


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.
May 7 '09 #8

P: 675
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:
Expand|Select|Wrap|Line Numbers
  1. dteField > dteLow And dteField <= dteHigh
This is NOT A VALID Where clause.

So, for some code for the Where clause, something like:
Expand|Select|Wrap|Line Numbers
  1. dteLow = txtService_Date       'First day of month
  2. dteHigh = DateAdd("m", 1, dteLow)    'First day of following month
  3. 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdRunReport_Click()
  2. 'Check for valid date
  3. If Not IsDate(txtService_Date) Then
  4.     MsgBox "Date is not valid, please try again"
  5.     Exit Sub
  6. End If
  7. 'Check for Date not in mmmm-yyyy format
  8. '    You don't want operator to enter something like 7/2/05, 
  9. '            which will pass the IsDate Test
  10. '    This isn't so easy, and is another problem, so no code here, FOR NOW!
  12. 'Build Report Title
  13. Select Case optGroupName
  14. Case 1
  15.     stReportTitle = "Report 1 - AAA"
  16. Case 2
  17.     stReportTitle = "Report 2 - BBB"
  18. Case 3
  19.     stReportTitle = "Report 3 - CCC"
  20. Case Else
  21.     MsgBox "No Report Selected"
  22.     Exit Sub
  23. End Select
  25. 'Build Where Clause
  26. dteLow = txtService_Date       'First day of month
  27. dteHigh = DateAdd("m", 1, dteLow)    'First day of following month
  28. stWhere = "dteField > #" & dteLow & "# And dteField <= #" & dteHigh & "#"
  30. 'Run Report
  31. OpenReport stReportTitle , acViewPreview, stWhere 
  32. 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.
May 7 '09 #9

Expert 100+
P: 1,356
Don't you have several people trying to help you with this issue here at your previous thread?
May 7 '09 #10

Expert 100+
P: 1,356
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.
May 7 '09 #11

P: 18
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.
May 7 '09 #12

P: 675
Back in the days of punch-cards to input data, people used to code
Expand|Select|Wrap|Line Numbers
  1. If Col4 = "Y" Then
  2. ...
  3. Else
  4. ...
  5. 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
May 7 '09 #13

Expert 100+
P: 1,356
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.

Exactly why I didn't ding ya. :)

I'm going to go ahead and merge these though. ok
May 7 '09 #14

P: 18
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdPrint_Click()
  2. Dim stWhere As String
  3. Dim stReportName As String
  4. Dim dteLow As String
  5. Dim dteHigh As String
  6. Dim lngView As Long
  8. 'stDateField = "[Service_Date]"
  9. lngView = acViewPreview
  12. If Not IsNull(Me.txtServiceDate) Then
  13.     If Not IsDate(Me.txtServiceDate) Then
  14.         MsgBox "The service date entered is not a valid date. Please enter a valid date.", vbOKOnly, "Invalid Service Date!"
  15.         Exit Sub
  16.     End If
  17. Else
  18.     MsgBox "You must enter a service date to continue.", vbOKOnly, "Not Service Date Entered!"
  19.     Exit Sub
  20. End If
  22. dteLow = Me.txtServiceDate
  23. dteHigh = DateAdd("m", 1, dteLow)
  24. stWhere = "[Service_Date] > #" & dteLow & "# And [Service_Date] <= #" & dteHigh & "#"
  26. Select Case [fraReports]
  27.     Case Is = 1
  28.         DoCmd.OpenReport "Court Placement", lngView, , stWhere
  29.     Case Is = 2
  30.         DoCmd.OpenReport "Not IV-E Eligible", lngView, , stWhere
  31.     Case Is = 3
  32.         DoCmd.OpenReport "Special Adoption Consideration", lngView, , stWhere
  33.     Case Is = 4
  34.         DoCmd.OpenReport "Traditional Foster Care", lngView, , stWhere
  35.     Case Is = 5
  36.         DoCmd.OpenReport "Treatment Homes", lngView, , stWhere
  37.     Case Else
  38.         MsgBox "You have to select a report to print first.", vbOKOnly, "No Report Selected!"
  39.         Exit Sub
  40. End Select
  41. End Sub
Again thanks to all who helped. This is by far the best forum for "Newbie"s like me.
May 7 '09 #15

P: 18
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.
May 7 '09 #16

Expert 100+
P: 1,356
Cool glad you've got it. Oh and in case your interested...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_NoData(Cancel As Integer)
  2.     MsgBox "There is no data for the criteria requested!" & vbCrLf & vbCrLf & "Closing report...", vbInformation
  3.     Cancel = True
  4. End Sub
May 7 '09 #17

P: 18
That is similar to what I have. I am getting a run time error '2501':
The Open Report Action was Canceled.

End Debug
May 7 '09 #18

Expert 100+
P: 1,356
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.
Expand|Select|Wrap|Line Numbers
  1. Debug.print strWhere
  2. Select Case [fraReports]
May 7 '09 #19

Expert Mod 15k+
P: 31,707
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).
May 7 '09 #20

P: 18
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.
May 8 '09 #21

P: 18
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?
Attached Images
File Type: jpg untitled.jpg (8.3 KB, 454 views)
May 8 '09 #22

Expert Mod 15k+
P: 31,707
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.
May 8 '09 #23

P: 18
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.
May 8 '09 #24

Expert Mod 15k+
P: 31,707
Well, if you're still convinced of that, then I can only bid you good luck and adieu.
May 8 '09 #25

P: 18
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.
May 8 '09 #26

Post your reply

Sign in to post your reply or Sign up for a free account.