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

    Report Balance (2000)

    I have a report where the purpose is the show any payments that were either allocated <> zero. The report is running fine. My problem is that when the report opens, I want it to only open to show payments with a balance of <> zero. The "balance" field on the report is not calculated in the query associated with the report I can't figure out how to get the correct answer. I've tried alot of approaches and get error messages.

    Thanks,
    Leesha
    Attached Files Attached Files

  2. #2
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts

    Re: Report Balance (2000)

    Just a thought, as I haven't had time to examine your problem in detail: Have you tried creating a query that lists, say, the account ID for each record that doesn't have a zero balance, then creating an inner join so that the query supporting the report only has the records to be reported on?

    From the quick look I had, I'd say the problem might be that you're trying to filter not on the detail line, but on one of the grouped items. (I also notice that you have a duplicate grouping, but didn't investigate whether there's a deliberate reason for that.)

    Good luck!

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

    Re: Report Balance (2000)

    Why don't you run the report from a form, or rather from a DoCmd.OpenReport command then you just need to do the following to test for non-zero outstanding payments.
    DoCmd.OpenReport stDocName, acPreview, , "Balance<>0"
    Also change the query behind the report to:
    SELECT tblPayment.CustomerID, [salutation] & " " & [firstname] & " " & [middleinitial] & " " & [lastname/businessname] & " " & [suffix] AS Expr1, tblDemographics.[LastName/BusinessName], tblPayment.PayID, tblPayment.DatePaid, tblPayment.Amount, tblPaymentAllocations.InvoiceNoReference, tblPaymentAllocations.AmountAllocated, [Amount]-Val(nz(DSum("AmountAllocated","tblPaymentAllocatio ns","PayID=" & [tblPaymentAllocations].[PayID]),0)) AS Balance
    FROM tblDemographics INNER JOIN (tblPayment INNER JOIN tblPaymentAllocations ON tblPayment.PayID = tblPaymentAllocations.PayID) ON tblDemographics.AccountID = tblPayment.CustomerID
    ORDER BY tblPayment.DatePaid, tblPaymentAllocations.InvoiceNoReference;
    Note I have changed the where clause of the DSum to test for PayID instead of invoice number.

    If I'm outta the ball park, just let me know.

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

    Re: Report Balance (2000)

    Thank you Allison and Pat for your posts.

    Pat, your suggestions did exactly what I was looking for. It's a weight off my shoulders as I just couldn't get it.

    Thanks,
    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
  •