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

    Forumula issue (2000)

    It's not my day! I have a report that needs to give the amount remaining after allocations have been applied to an invoice. The formula I'm using is [InvoiceTotal]-sum([AmountAllocated]). This works fine unless there has been nothing allocated. In those instances no value is returned. I would expect a Zero, but instead there is nothing.

    The second issue is that on the report footer I need to have a grand total for the balance remaining. I've tried everything under the sun and never get the correct number. For example, I even tried just putting in the control source [AmountAllocated] to see what came up. I get $293.00 when in fact only at total of $75.00 has been applied. I have not idea where this number is coming from. The value needs to represent the sum of the outstanding balances.

    Any help with these two issues would be greatly appreciated!

    Thanks,
    Leesha

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

    Re: Forumula issue (2000)

    You may have to enclose your expression in a NZ function, I presume that this expression is in a query, eg.
    [InvoiceTotal]-sum(Nz([AmountAllocated]),0)

    What is the balance you are talking about, is it the sum on Invoices minus the sum of Payments? If so, then AmountAllocated has nothing to do with a balance unless you are after the balance of Payments - PaymentsAllocated.
    Do you calculate the balance in the query?

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

    Re: Forumula issue (2000)

    >>You may have to enclose your expression in a NZ function, I presume that this expression is in a query, eg.
    [InvoiceTotal]-sum(Nz([AmountAllocated]),0)

    No, I took this forumla out of this query although I'm using it elsewhere. In this situation I'm simply putting a textbox on the report that with a formula to find the total for that invoice after the payment allocations have been applied. I'm using payment allocations to the invoice vs payment in general (Amount) as the amount allocated does not always get applied in full to the invoice number, but rather to various invoice numbers. The purpose of the report is to show the InvoiceTotal / invoice number, the date(s) / amount(s) allocated and the resulting balance, if any. The report contains all invoices for the Account that are > 0.

    Leesha

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

    Re: Forumula issue (2000)

    We could go into a whole scenario of questions and answers, like what's the source of the report, etc etc.
    You could just post a zipped version of your database. I will then make all the changes I need to, and tell you via return post what I changed.

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

    Re: Forumula issue (2000)

    OK, will do in a bit. Gotta strip it down again.

    Leesha

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

    Re: Forumula issue (2000)

    Here ya go!

    Leesha
    Attached Files Attached Files

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

    Re: Forumula issue (2000)

    When you say you want to show invoices and how much has been applied to each invoice by way of allocations, this wont show the balance of how much the Customer owes. It will just show how much of the payments have been allocated not how much has been paid against those invoices.
    Or am I missing something?

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

    Re: Forumula issue (2000)

    I need to show how much has been applied to the invoice and then what the amount remaining is so when a batch invoice is sent the account can can see their overall total or grand total and then can see what is owed on each individual invoice. When I run the report I need to filter it so only the invoices with any balance come up on the report.

    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
  •