Filter Report by Date Range

[smithj14]

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.

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]

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?

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]

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

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]

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.

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.