Filters ( 2k sr1)

Topic

New Reply

I am trying to re-use a query that is already in use, but I need to have the user input a date as the criteria without changing the query. Would this be a filter?

What is the code for this? Please be gentle, I am still at the beginning of the learning curve. Thanks.

Reply | Quote

Replies

What do you mean by already in use? From the rest of you question, I’d guess that a filter is what you want but that may not be what you really need. We need more information. What exactly are you doing with this query?

Reply | Quote

I currently have a report that prints based upon the information currently stored in the tables. The users would like to be able to print these reports based upon a date in the past and see what the data looked like at that time.

The data consists names, amounts invested, dollars distributed back, capital calls and of course the dates for each of these.

Currently, the report takes every bit of data into consideration and I need to be able to ask the user what date (if any) would you like to see this report generated from? Make sense?

Reply | Quote

I suspected it was something like that. In that case, you have three options:

1) Open the report either for all dates for for a specific date range in the first place. To do this, you would need some code and use the InputBox function to get the dates from the customer and then create the WhereCondition string and pass that to the report in the DoCmd.OpenReport statement.

2) Open the report for all dates but then apply a filter which would be the equivalent of the WhereCondition in #1.

3) Put date criteria into the underlying recordsource for the report. Something like “Between [Enter Beginning Date] And [Enter Ending Date]”. You could get much more elaborate with the criteria in order to handle the use simply hitting an enter instead of passing a date in, but the idea is the same.

Reply | Quote

Thanks Charlotte,

Number 1 or Number 2 would work for my purposes. However, I don’t know how to write the code. Can someone help me get to the next step?

Reply | Quote

How were you intending to open the report, from a menu/switchboard? If so, is the switchboard something created with the Switchboard Manager utility or a form you created? The answer determines where the code actually lives. If you just want to open the report and have it ask for the dates, you have to do it differently. Do you have any code at all? And how many records are you dealing with here? Filters can be slow on large recordsets.

Reply | Quote

The record set is small (182 records) and will grow at a somewhat slow pace. We only have 3 users – one inputs, one prints and one reviews the data. I have created command buttons to print the various reports for the user that prints.

Here is my code, as embarrassing as it may be:

Private Sub Report_Open(Cancel As String)
Dim intFilter As Integer
strFilter = InputBox(“Enter Date”, “Need Data”)
If strFilter “” And strFilter ” ” Then
Me.Filter = “[MaxOfDate] = ‘” & strFilter & “‘”
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End Sub

I wanted the date to default to current and use all of the data if no date was entered.

Thanks.

Reply | Quote

I assume from the name that this is an event procedure from within the report itself. This is one of your alternatives. The other is to put similar but tweaked code into the event procedure behind the button click and to pass a where condition in the OpenReport statement. That eliminates the need for the filter in the report itself and is faster on larger recordsets. If you want to do it in the report using a filter, change your code to this:

Private Sub Report_Open(Cancel As String)
  Dim intFilter As Integer
  Dim intLoop As Integer
  Dim strDate As String
  Dim strFilter As String
 
  For intLoop = 1 to 2
    strDate = vbNullString
    Select Case intLoop
      Case 1
        strDate= InputBox("Enter Beginning Date", "Need Data")
        If IsDate(strDate) Then
          strFilter = "[MaxOfDate]>=#" & strDate & "#"
        End If
      Case 2
        strDate = InputBox("Enter Ending Date", "Need Data")
        If IsDate(strDate) Then
           if Len(strFilter)>0 Then
             strFilter = strFilter & " AND "
          End If 
          strFilter = strFilter & "[MaxOfDate]0 Then
      Me.Filter = strFilter 
      Me.FilterOn = True
    End If
End Sub

What this boils down to is that if they enter a beginning date, the report will include all dates greater than or equal to the date they entered. If they enter and ending date, the report will include all date less than or equal to that date. If they enter both, the report will return only those dates between the two and if they enter neither, it will return all records.

You could also provide a means of cancelling the report by testing the result of inputbox to see if they had hit the cancel button and, if so, setting the Cancel argument to True.

Reply | Quote

It appears that I am a little more messed up than I thought – my code was placed in the On Open property of the report itself.

I am unsure what you mean by “button click”. Does that mean On Enter of the command button?

Reply | Quote

Now I tried to place the code into the On Active property of the report and I get an error that says: Compile Error End Select without Select Case.

Any ideas?

Reply | Quote

Ok, I am now very very close. Just one question – the input box appears more than once. Why is this?

Reply | Quote

Because you need two dates, a beginning and an ending date. Otherwise, you would only get a starting date, and they would get everything from that date forward. You could generate the ending date if you know for sure that they only want to run the report for a specified month, etc., but it allows more flexibility if you let them tell you the specific period they want.

Reply | Quote

No, it means the OnClick event of the Button. In that event, where you now are probably doing something like

DoCmd.OpenReport “ReportName”

you could use code to have the users input dates and create a string like your filter string, but you would pass it as the WhereCondition argument of the OpenReport method. So your command would look something like this:

DoCmd.OpenReport “ReportName”,,strFilter

If you used this approach, you wouldn’t need the code in Report_Open.

Reply | Quote

The input box continues to appear indefinitely. The loop won’t end. Why is that? I understand that 2 boxes should appear – one for the beginning date and then another for the end date. But then the beginning date box appears again and the end, etc.

Reply | Quote

Then post your code. I used the For intLoop = 1 to 2, which will only fire twice, not indefinitely. If you used something else, that’s your problem.

Reply | Quote