Need help with Filters in a Report

[DanaPach]

I am adding a filter via VB directly into the 'Filter' under the 'Data tab'. It is working, however, I need to add 2 types of criteria but don't know how to set this up. The reason I am adding this filter in the report is because it is reading from a crosstab query - and does not recognize a criteria such as Forms![frm_FY]![txtQuarter].

Here is what I have thus far in my code:
Reports!rpt_Work_Order_Completion_Summary.Filter = "FY=" & Me.txtFY

I need to also add "Quarter" & me.txtQuarter

Any help will be greatly appreciated.

 

[mgolla]

Change this:

Reports!rpt_Work_Order_Completion_Summary.Filter = "FY=" & Me.txtFY

To This:

Reports!rpt_Work_Order_Completion_Summary.Filter = "FY=" & Me.txtFY & " AND QUARTER=" & Me.txtQuarter

Make sure the txtFY & txtQuarter are numeric values otherwise you will need to add single quotes like this:

Reports!rpt_Work_Order_Completion_Summary.Filter = "FY='" & Me.txtFY & "' AND QUARTER='" & Me.txtQuarter & "'"

HTH
Mike

Dooobie...Doobie......Dooo

 

[chris153]

Hello,

You can use parameters in a cross-tab query such as Forms![frm_FY]![txtQuarter] if you define them in Query, Parameters.

Otherwise just build up the filter like a sql string :

Reports!rpt_Work_Order_Completion_Summary.Filter = "FY=" & Me.txtFY & " AND Quarter=" & me.txtQuarter

Hope this helps !

 

[DanaPach]

This worked perfect!!! Thank you so much for your help on this -- you saved me from beating my head any further on the table.

 

[DanaPach]

Chris,
Thanks for your help also, it worked great. I am curious as to how to define in query parameter. Can you assist me with this for future reference. Thanks again to both you and Mike for your assistance.

 

[chris153]

Hello,

When you build a query and add a criteria e.g. [Please enter the id], you can define it as a parameter so that Access knows what type of data type it should be.

In the Menu Bar of the query builder goto Query, Parameters... and type in the parameter that you want to define and choose its data type.

You can use parameters in cross-tab queries but they must all be defined in this way.

good luck

Chris