Results 1 to 7 of 7
  1. #1
    Join Date
    May 2011
    Posts
    11

    Unanswered: Filtering Report By Numeric Value

    Hi, need your help again. I am trying to create a button on my form which will open a filtered report. I have already created the report and totals have been created for individual customers (who have their unique ID). The total is in my ID Footer section (report design view). I want the report to permanently show the customers whose totals are 3600 (and they are eligible for a surprise gift). The rest will be filtered out until they meet the criteria. I have tried the following but there is something wrong.

    Private Sub Command26_Click()
    DoCmd.OpenReport "EliParameterReport", acViewPreview, "[Sum_Of_Purchases] = " & 3600
    End Sub

    "Sum_Of_Purchases" is the Total field

    Like I said I am still a novice. Thank you in advance

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I think you have it in the wrong position, and you don't need to concatenate a static value. Try

    DoCmd.OpenReport "EliParameterReport", acViewPreview, , "[Sum_Of_Purchases] = 3600"
    Paul

  3. #3
    Join Date
    May 2011
    Posts
    11

    Question

    Thank you for the quick reply. I live in the Eastern Hemisphere. When I started this thread, it was 4.30 a.m. I just went to sleep after that.

    OK, I tried what you posted and a parameter box pops up and asks for Sum_Of_Purchases. Whether I enter anything or not, the report displays 0 results. It could be one of two problems (I think).

    1.) Is it because the control is an unbound text box?
    2.) Or is it because the control in situated in the ID Footer section (grouped
    by in the report).

    When I change to a bound text box in the detail section, it works! (Unfortunately that is not what I want) The query that I base the report on is made up of 3 queries. The end result is working fine when I open the report on its own.

    Thank you......

    (wow, now I can put a lot of dots.....)

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    For that method to work, Sum_Of_Purchases has to be a field in the report's source table/query. Can the calculation be moved to there?
    Paul

  5. #5
    Join Date
    May 2011
    Posts
    11

    Thumbs up

    Yes. Thank you. I went back to the query, clicked on the Totals on the ribbon,
    Grouped By ID to Count and Purchases to Sum, created a Total in the next column of the query in Design View and its working.

    Hey Paul, you always manage to put me in the right direction

    Thank you again. Simple and logical. That's the best

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Glad you got it working!
    Paul

  7. #7
    Join Date
    May 2011
    Posts
    11
    Hi again, I realized one thing. When you have too many columns in a Group By (Totals) Query, it goes haywire. I finally settled for only four columns, namely
    Field Total
    ID - Group By
    SubID - Group By
    NAME - Group By
    PURCHASES - Sum (which once you sum, converts to SumOfPURCHASES)

    Then I created a button

    with the following On Click Event Procedure
    DoCmd.OpenReport "EliReport", acViewPreview, , "[SumOfPURCHASES]=3600"

    EliReport is the Grouped By ID Summary Report with Totals. The report is created from the query.

    Once you click the button, it only shows all Purchases Totals (Grouped By ID) which equal to 3600

    I think the trick is to have as few fields as possible in the Query
    Last edited by Abu Haniffa; 05-21-11 at 07:01.

Posting Permissions

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