Results 1 to 15 of 15
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filters ( 2k sr1)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filters ( 2k sr1)

    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?
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filters ( 2k sr1)

    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?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filters ( 2k sr1)

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

  5. #5
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filters ( 2k sr1)

    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?

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filters ( 2k sr1)

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

  7. #7
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filters ( 2k sr1)

    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.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filters ( 2k sr1)

    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:

    <pre>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]<=#" & strDate & "#"
    End If
    End Select
    Next intLoop

    If Len(Trim(strFilter))>0 Then
    Me.Filter = strFilter
    Me.FilterOn = True
    End If
    End Sub</pre>

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

  9. #9
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filters ( 2k sr1)

    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?

  10. #10
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filters ( 2k sr1)

    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?

  11. #11
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filters ( 2k sr1)

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

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filters ( 2k sr1)

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

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filters ( 2k sr1)

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

  14. #14
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filters ( 2k sr1)

    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.

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filters ( 2k sr1)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •