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

    Query Returns Null Value (2000)

    High All,

    I have a query with the following SQL:

    SELECT tblBilling.AccountID, tblDemographics.[LastName/BusinessName], tblPaymentAllocations.PayID, tblBilling.DateWorked, tblBilling.InvoiceTotal, tblBilling.InvoiceNo, tblBilling.DateBilled, tblPaymentAllocations.AmountAllocated, tblPayment.DatePaid, [invoicetotal]-[amountallocated] AS Balance
    FROM tblPayment RIGHT JOIN (tblDemographics INNER JOIN (tblBilling LEFT JOIN tblPaymentAllocations ON tblBilling.InvoiceNo = tblPaymentAllocations.InvoiceNoReference) ON tblDemographics.AccountID = tblBilling.AccountID) ON tblPayment.PayID = tblPaymentAllocations.PayID
    ORDER BY tblDemographics.[LastName/BusinessName], tblBilling.InvoiceNo, tblPayment.DatePaid;

    The goal is to show the balance on an invoice after a payment allocation has been applied. This works fine. My problem is that when there has not been anything allocated the query returns a null value when what I want it to return is the original InvoiceTotal.

    My question, is there a way to do an if/than statement in a query that tells that query that if the balance field is null than the field = InvoiceTotal? If so, how would I write this or, is there a better/different approach.

    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: Query Returns Null Value (2000)

    Try this:
    SELECT tblBilling.AccountID, tblDemographics.[LastName/BusinessName], tblPaymentAllocations.PayID, tblBilling.DateWorked, tblBilling.InvoiceTotal, tblBilling.InvoiceNo, tblBilling.DateBilled, nz([AmountAllocated],0) AS AmtAlloc, tblPayment.DatePaid, [invoicetotal]-[AmtAlloc] AS Balance
    FROM tblPayment RIGHT JOIN ((tblDemographics INNER JOIN tblBilling ON tblDemographics.AccountID = tblBilling.AccountID) LEFT JOIN tblPaymentAllocations ON tblBilling.InvoiceNo = tblPaymentAllocations.InvoiceNoReference) ON tblPayment.PayID = tblPaymentAllocations.PayID
    ORDER BY tblDemographics.[LastName/BusinessName], tblBilling.InvoiceNo, tblPayment.DatePaid;

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

    Re: Query Returns Null Value (2000)

    Pat, you're wonderful!!! That worked beautifully. Now, what does the NZ stand for in nz([AmountAllocated],0) ?

    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: Query Returns Null Value (2000)

    NZ means Null to Zero. What it does is evaluate the variable AmountAllocated to see if it is Null and if so will substitute the 2nd argument (in this case zero).

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

    Re: Query Returns Null Value (2000)

    Cool!! I've bookmarked that one for future use.

    <img src=/S/doh.gif border=0 alt=doh width=15 height=15> I've got another question. I think its related so I'll keep it here vs start a new thread.
    When I open the report I want the report to only show invoices with <> Zero balance. The control that would determine that contains the following forumula ....=[InvoiceTotal]-Sum([AmtAlloc]). It is not actually a control in the query. I used the following code to open the report but it doesn't work:

    DoCmd.OpenReport "rptBatchInvoice", acViewPreview, , "txtTotalBalanceDue <> 0"

    I named the control txtTotalBalanceDue in the "other" portion of the parameters. What am I doing wrong? All invoices are showing.

    Thanks,
    Leesha

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

    Re: Query Returns Null Value (2000)

    You don't reference the text box of the report, but instead you have to reference the source of the report. In this case it's the query that is the source of the report. In it I think you have a field called Balance, so your OpenReport becomes:

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

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

    Re: Query Returns Null Value (2000)

    Nope this didn't work. It makes sense to me that I'd reference the query, however there is not a field in the query that gives me the results of the formula I mentioned above. When I tried using the formula you gave me awhile back (in another thread) it worked fine unless an allocation had not been made. In those instances I'd get an error message. So I guess my next question is how do I code a field that will give me the TotalInvoice - sumAllocAmt (for each invoice) that doesn't blow up if there is nothing allocated?

    Leesha

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

    Re: Query Returns Null Value (2000)

    Hi Leesha
    Would you post your query here, and I'll take a look at it in the database you sent me.

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

    Re: Query Returns Null Value (2000)

    Pat, the original one is in the first the post and now its the one that you sent me. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Leesha

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

    Re: Query Returns Null Value (2000)

    You got me on that one, try this:

    SELECT tblBilling.AccountID, tblDemographics.[LastName/BusinessName], tblPaymentAllocations.PayID, tblBilling.DateWorked, tblBilling.InvoiceTotal, tblBilling.InvoiceNo, tblBilling.DateBilled, tblPaymentAllocations.AmountAllocated, tblPayment.DatePaid, nz(dsum("AmountAllocated","tblPaymentAllocations", "InvoiceNoReference=" & tblBilling.InvoiceNo),0) as AmtAlloc, [invoicetotal]-[AmtAlloc] AS Balance
    FROM tblPayment RIGHT JOIN (tblDemographics INNER JOIN (tblBilling LEFT JOIN tblPaymentAllocations ON tblBilling.InvoiceNo = tblPaymentAllocations.InvoiceNoReference) ON tblDemographics.AccountID = tblBilling.AccountID) ON tblPayment.PayID = tblPaymentAllocations.PayID
    ORDER BY tblDemographics.[LastName/BusinessName], tblBilling.InvoiceNo, tblPayment.DatePaid;

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

    Re: Query Returns Null Value (2000)

    I tried the new SQL but it doesn't give me the balance overall with all the allocations deducted. For example, I have an invoice for 106.00. There is an allocation of 50.00 and an allocation of 56.00. The balance per the query is showing as 50.00.

    Leesha

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

    Re: Query Returns Null Value (2000)

    That's strange, I have an invoice for $106 and 2 allocations $25 and $50 which leaves a balance of $31 which is correct.

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

    Re: Query Returns Null Value (2000)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/bash.gif border=0 alt=bash width=35 height=39>
    I just redid the SQL, retried it and now it works................haven't got a clue why, but am happy it works. My brain must be elsewhere this morning.

    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
  •