filter problem (97)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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))

Reply | Quote

ok this works better as query criteria but I

Reply | Quote

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

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

Reply | Quote

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

Reply | Quote