Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Filtering a report (2000)

    I would like know if it is possible to filter a report. I would like to set a limit on it so that if the returned value from a formula is zero, then the associated invoice or billing dates do not show.

    The formula is [invoiceTotal] - sum[amountAllocated] The form is sorted by AccountID and DatePaid.

    Thanks!

    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering a report (2000)

    Assuming that you open the report using DoCmd.OpenReport, you can specify a Where condition when opening the report. It would be best to include the balance (the result of the formula) in the Record Source of the report. Say that you name this field Balance and that the report is named rptMyReport; you could use

    DoCmd.OpenReport "rptMyReport", acViewPreview, , "Balance <> 0"

    If you already had a Where condition, concatenate the conditions with " And ", for example

    DoCmd.OpenReport "rptMyReport", acViewPreview, , "AccountID = " & Me.AccountID & " And Balance <> 0"

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filtering a report (2000)

    Thanks Hans, I'll give this a shot tonight!

    Leesha

  4. #4
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filtering a report (2000)

    Hi Hans,

    I tried the first version of the code and got the following error message "You tried to execute a query that does not include the specified expression 'CustomerID' as part of an aggregate function." I haven't as of yet written a where condition but that will be coming so I appreciate your giving me the code!!

    The SQL for the data source for the report is .................

    SELECT tblPayment.CustomerID, tblPayment.DatePaid, tblPayment.Amount, tblPaymentAllocations.InvoiceNoReference, tblPaymentAllocations.AmountAllocated, [Salutation] & " " & [FirstName] & " " & [MiddleInitial] & " " & [LastName/BusinessName] & " " & [suffix] AS Expr1, [amount]-Sum([amountallocated]) AS Balance
    FROM (tblDemographics INNER JOIN tblPayment ON tblDemographics.AccountID = tblPayment.CustomerID) INNER JOIN tblPaymentAllocations ON tblPayment.PayID = tblPaymentAllocations.PayID;

    Thanks!
    Leesha

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Filtering a report (2000)

    Try this, it may satisfy your requirements, it might not:

    SELECT tblPayment.AccountID, tblPayment.DatePaid, tblPayment.Amount, tblPaymentAllocations.InvoiceNoReference, [Salutation] & " " & [FirstName] & " " & [MiddleInitial] & " " & [LastName/BusinessName] & " " & [suffix] AS AcctName, [amount]-Sum([amountallocated]) AS Balance
    FROM tblDemographics INNER JOIN (tblPayment INNER JOIN tblPaymentAllocations ON tblPayment.PayID = tblPaymentAllocations.PayID) ON tblDemographics.AccountID = tblPayment.AccountID
    GROUP BY tblPayment.AccountID, tblPayment.DatePaid, tblPayment.Amount, tblPaymentAllocations.InvoiceNoReference, [Salutation] & " " & [FirstName] & " " & [MiddleInitial] & " " & [LastName/BusinessName] & " " & [suffix];

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filtering a report (2000)

    OK, so now I'm really in awe. It works exactly like I was looking for. Initially I had a few errors and some parameter issues, but I figured those out on my own (will wonders never cease) by making the necessary changes to the report and now its all set to go!!!

    Hope the house hunting is coming along!

    Thanks again,
    Leesha

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •