[AccessD] Report Filter Problem

Rocky,

    It would be more convenient to apply the criteria to record source of the report itself. Modified code as given below, should get you the desired results:

'==========================================
    Dim Cdn As String
    
    Cdn = "(SELECT Count(*) FROM " & _
            "Q_Stock AS Q1 WHERE " & _
            "Q1.Product = Q_Stock.Product " & _
            "AND Difference >= 0.01) > 0"

    If Forms!frmPricePaidVersusContractReport!fraPrint = 1 Then
        DoCmd.OpenReport "R_Stock", acViewPreview
    Else
        DoCmd.OpenReport "R_Stock", acViewPreview, , Cdn
    End If
'==========================================

    Note - Q_Stock is the assumed name of unfiltered query acting as permanent record source for the report named R_Stock, while Product and Difference are the field names in this query. You can substitute by correct names of report, query and fields as actually existing at your end.

Best wishes,
A.D.Tejpal
------------

  ----- Original Message ----- 
  From: Rocky Smolin at Beach Access Software 
  To: 'Access Developers discussion and problem solving' 
  Sent: Tuesday, December 04, 2007 23:40
  Subject: [AccessD] Report Filter Problem


  Dear List:
   
  I have a report grouped on Product.  In the detail there are two number -
  standard and actual cost.  There's an option on the calling form to print
  all records or only records where the difference is > .01.  In the report's
  Open event, then, I set the filter:
   
      If Forms!frmPricePaidVersusContractReport!fraPrint = 1 Then
          Me.Filter = ""
          Me.FilterOn = False
      Else
          Me.Filter = "Difference >=.01"
          Me.FilterOn = True
      End If

  All's well so far.
   
  Then the client said to change the report so that if the option to print
  only if DIFFERENCE > .01 and ANY ONE record in the product qualified, then
  print all records for that product.
   
  So in the format event of the header of the Product I do a little code to
  see if ANY ONE record qualifies and if so I turn the filter off:
   
  ShowDetail:
      Me.Filter = ""
      Me.FilterOn = False

  and then in the Format event of the Product Footer I conditionally turn the
  filter back on again with same code  I used in the open event.
   
  Problem is that it goes into a loop which I traced down to the report being
  run over and over again.  I put a break point in the Open event, and it just
  keeps running the report again and again.
   
  What is going wrong here - or better yet - what's the solution?
   
  MTIA
   
  Rocky