Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    118

    Question Unanswered: Whats the best way to filter data on a reort?

    Hi,

    I'm trying to create a little custom filtering utility on reports and I am not sure about the best way to go about this. I want to make it as flexible as possible. I will be creating the reports myself, but the user will need to be able to filter data on those reports via a form.

    The way I tried to do it before is building the entire SQL string and storing that in a reference table. Unfortunatly the is a problem with that approach. Apart from the fact that its harder to maintain and edit the queries some reports may require a "where" clause in their query as default and some will not.

    Idealy I just want to create a query in design view and somehow filter the report. I was thinking of using the report filter property, but I am not sure how to go about that, if it's limited to just one statement or can I use "AND" connectors?


    Any advice would be appreciated, thanks.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    I write/build my own queries and filters ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I would still recommend using VBA for this one. Building the "filter" for a report is pretty much identicle to building a where clause. I generally store the entire query, sans the where clause, in the record source for the report itself. Then in the on open event for the report, I dump the original recordsource into a variable, construct the where/order by clauses, tack them on to the original recordsource, then assign the whole thing back to the reports recordsource.

    A super quick 'n dirty version may look like this:
    Code:
    Dim src As String
    Dim frm As Form
    Dim where As String
    Dim orderby As String
    
    src = Me.RecordSource
    frm = forms!yourParameterForm
    
    where = "yourField LIKE '" & nz(frm!cmbYourField,"*") & "'"
    where = "yourOtherField = " & frm!cmbYourOtherField
    where = " WHERE " & where
    
    If frm!SortOrder.value = "Ascending" Then
       orderby = " ASC"
    else
       orderby = " DESC"
    End If
    
    orderby = frm!OrderBy.value & orderby
    
    Me.RecordSource = OldSrc & where & orderby
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Mar 2004
    Posts
    118
    Teddy,

    Do you actually store the SQL for each report in a reference table?

    I want to try and use queries saved in access rather than pure SQL. It will be easier to change.

    What I have now is a Form in whcih you build a basic Where clause and then save it in a table.

    I then have a separate form in which you choose a report, choose filter, if any, and run it. What i did previously is store the whole SQL statement and assign it to RecordSource.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    --------------------
    I generally store the entire query, sans the where clause, in the record source for the report itself.
    --------------------
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Mar 2004
    Posts
    118
    Exactly, So is there a way to get the SQL from a separate saved query and store just the name?

    Or would I have to use the report filter property?

Posting Permissions

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