Problem filtering my query

[jonman1124]

Hi, I am fairly new to Access as well as VBA.

My current setup has a report called "qall summary" which is generated by a bunch of queries all going back to a single query which collects the raw data together, called qall. I want to be able to:
1) generate this report based on ALL the data queried by qall
2) generate this report based after filtering the data generated by qall on a field called sellers, pulling only the information where sellers = 'CIBC'
3) same as #2 where sellers <> 'CIBC'

what i have right now is a form called report_switchboard where can click on an option group to filter the data generated by qall, but when i click on the button to preview the report, i see a report based on all the data, regardless of which option button i chose. I can see that the data has been filtered when i click on the option buttons, but the report does not reflect that. can someone help out?

this is what my code looks like:
Private Sub Preview_Click() 'preview one of two reports
Dim strreporttype As String

If reporttype = 1 Then
strreporttype = &quot;all summary&quot; 'percentage based report
Else
strreporttype = &quot;all summary delinq loan count&quot; 'count based
End If

DoCmd.OpenReport strreporttype, acViewPreview
DoCmd.Close acForm, &quot;report_switchboard&quot;

End Sub
-----------------------------------------------
Private Sub selleroption_AfterUpdate()
'apply or remove the filter for the option the user chose.

Select Case selleroption

Case 1
Me.Filter = &quot;seller = 'CIBC'&quot;
Me.FilterOn = True

Case 2
Me.Filter = &quot;seller <> 'CIBC'&quot;
Me.FilterOn = True

Case 3
Me.FilterOn = False

End Select

End Sub

 

[Frink]

Hallo,

When running code from a Form, Me refers to the form, ie it is the same as Forms!report_switchboard. Therefore the filter you're setting up is on the Form, not the Report.

Try changing the line:

DoCmd.OpenReport strreporttype, acViewPreview

to

Select Case me!selleroption
  case 1
    DoCmd.OpenReport strreporttype, acViewPreview,,"seller='CIBC'"
  case 2
    DoCmd.OpenReport strreporttype, acViewPreview,,&quot;seller<>'CIBC'&quot;
  case else
    DoCmd.OpenReport strreporttype, acViewPreview
end select

This specifies a Where clause to be applied when the report is opened.
(and you can remove the selleroption_AfterUpdate code)

Hope that helps/works,

- Frink

 

[jonman1124]

frink,
what if seller is not a field in the query the report is based on? basically, i'm trying to choose between using the entire recordset and a subset of the recordset (namely, where &quot;qall.seller = 'cibc'&quot; qall, being the query which pools my entire recordset together.)

 

[Frink]

Hallo,

You can't filter on a field which isn't there, so you'll have to include seller in your underlying query.

Unless I'm missing something...

- Frink

 

[jonman1124]

i figured it out. i was simply trying to filter the underlying recordset that was being pooled together by a query called qall. so all i did was create a form called switchboard with a txtbox called whichseller and a command button to generate the different reports i want. and in my query qall, i typed in the criteria under the seller field: &quot;forms!switchboard!whichseller or forms!switchboard!whichseller is null&quot;. now when i leave the txtbox blank, i get the full recordset reflected in my reports, and if i type in a specific criteria, my reports reflect a filtered recordset. yay! so simple now that i got it done