Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    filter problem (97)

    filter problem
    I created a report based on a query but must further restrict the report results. I

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter problem (97)

    Arage,

    I'd suggest using a query for your report's Record Source. Let the query filter out the records you don't want to see.

    Tom

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter problem (97)

    well bushaw, my report is based on a query that filters to a certain extent, it goes so far as to filter by promotionType and ManagerName, but since I

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter problem (97)

    I tried something like this as it fulfills the criteria for one of my managers & the events I want to see for him, but my query is coming up blank.

    IIf([PromotionType]="be" And [Manager]![Code]=1203,([EventNumber]=7 And [EventNumber]=14 And [EventNumber]=15))

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter problem (97)

    ok this works better as query criteria but I

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter problem (97)

    Arage,
    Why are you trying to filter in the detail section.
    You can set the Filter in the on open report event and set the filteron to true.
    Your selection seems also to be wrong

    <pre>Private Sub Report_Open(Cancel As Integer)
    Me.Filter = "PromotionType]="be" And [Manager]![Code]=1203 " _
    & "And ([EventNumber]=7 Or [EventNumber]=14 " _
    & "Or [EventNumber]=15)"
    Me.FilterOn = True
    End Sub
    </pre>

    Francois

  7. #7
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter problem (97)

    Arage,

    Well, your query could contain WHERE sub-clause something like ((EventNumber Between 3 And 30) And (PromotionType = "be") And (ManagerName = "ken williams")). Add one of these for manager, separated by "Or". This is a little cumbersome, but probably not much worse than the string of If-Thens in VBA.

    Perhaps a more elegant way to do this would be to create a link table that defines which events/promotions each manager is to "see" (e.g., each record has ManagerID, EventID, and PromotionID for each "allowed" combination). Use this link table to set up one-to-many relationships (the link table is on the many side) with the manager table, event table, and promotion table. A query built on this structure will then generate records only for those manger-event-promotion combinations that you have established in the link table.

    Tom

Posting Permissions

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