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

    Forumla Needed (2000)

    I have stripped down database with a query and the DB tables. I need to be able to find the balance remaining in a [Amount] from tblPayment after all allocations have been made to various invoices from the payment. The purpose is the be able to create an error report that the user can run to be sure that they have applied the total [Amount] from each payment and do not have a balance remaining or have applied more money than was paid.

    Thanks,
    Leesha
    Attached Files Attached Files

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

    Re: Forumla Needed (2000)

    Try this Leesha:

    SELECT PayID, Amount, DSum("AmountAllocated","tblPaymentAllocations","Pa yID=" & PayID) as AmtAllocated
    FROM tblPayment
    WHERE (tblPayment.Amount <> DSum("AmountAllocated","tblPaymentAllocations","Pa yID=" & PayID))

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

    Re: Forumla Needed (2000)

    Hi Pat,

    When I added your code to the query I'm using for this report I get an error message that the specified field paymentId could refer to more than one field. It's in tblPaymentAllocations and tblPayment.

    Thanks,
    Leesha

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

    Re: Forumla Needed (2000)

    So which one do you want it to refer to? You have to prefix it with the table name.
    eg. use tblPaymentAllocations.paymentId or tblPayment.paymentId

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

    Re: Forumla Needed (2000)

    Uhm, it doesn't matter to me. Do you have a suggestion or does it matter?

    Leesha

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

    Re: Forumla Needed (2000)

    Use tblPayment if you are using the query I suggested above.

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

    Re: Forumla Needed (2000)

    Ugh!! Still getting an error. Here's the SQL I tried.

    SELECT tblBilling.AccountID, tblDemographics.[LastName/BusinessName], [Salutation] & " " & [FirstName] & " " & [MiddleInitial] & " " & [LastName/BusinessName] & " " & [Suffix] AS Expr1, tblBilling.InvoiceNo, tblBilling.InvoiceTotal, tblPaymentAllocations.AmountAllocated, [InvoiceTotal]-[AmountAllocated]-Val(nz(DSum("AmountAllocated","tblPaymentAllocatio ns","InvoiceNoReference=" & tblPaymentAllocations.InvoiceNoReference & " AND PayAllocID <" & tblPaymentAllocations.PayAllocID),0)) AS Balance, tblPayment.Amount, tblPayment.DatePaid, DSum("AmountAllocated","tblPaymentAllocations","tb lPayment.PayID=" & [tblPayment].[PayID]) AS AmtAllocated, tblPayment.PayID
    FROM tblPayment INNER JOIN (tblDemographics INNER JOIN (tblBilling INNER JOIN tblPaymentAllocations ON tblBilling.InvoiceNo = tblPaymentAllocations.InvoiceNoReference) ON tblDemographics.AccountID = tblBilling.AccountID) ON tblPayment.PayID = tblPaymentAllocations.PayID
    WHERE (((tblPayment.Amount)<>DSum("AmountAllocated","tbl PaymentAllocations","tblPayment.PayID=" & "tblPayment. [PayID]")))
    ORDER BY tblDemographics.[LastName/BusinessName], tblBilling.InvoiceNo;

    Leesha

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

    Re: Forumla Needed (2000)

    Replace your entire query with the one I proposed earlier. Your query relates to invoices which has nothing to do with allocations of payments.

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

    Re: Forumla Needed (2000)

    Pat, the one you gave me earlier doesn't contain all the fields I need for the report. I posted the query and report earlier in the thread so you could see what the report looks like. The problem with the report is that I can't get it to open to show Payments that are <> zero because there is nothing in the query that accomplishes this.

    Leesha

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

    Re: Forumla Needed (2000)

    My query should show the differences. You can change my query to show the tblDemographics data, but you can't show invoices in this query since it just shows Payments and the Allocations on them.

Posting Permissions

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