[AccessD] Report Filter Problem

A.D.:

Much simpler than the solution I cooked up.  In the group header I check to
see if any detail record in the group has a difference >.01.  If so, I set a
flag - fPrint True else False.  Then, in the detail section:

If fPrint = True Then 
    Exit Sub
Else
    Me.MoveLayout = False
    Me.NextRecord = True
    Me.PrintSection = False
End If

so if fPrint is True all the records print. Otherwise, none.

Thanks and regards,

Rocky

 	
	

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
Sent: Friday, December 07, 2007 6:11 AM
To: Access Developers discussion and problem solving
Subject: Re: [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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.503 / Virus Database: 269.16.17/1176 - Release Date: 12/6/2007
11:15 PM