Query Returns Null Value (2000)

Topic

New Reply

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

Reply | Quote

Replies

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;

Reply | Quote

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

Thanks,
Leesha

Reply | Quote

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).

Reply | Quote

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

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

Reply | Quote

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”

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Pat, the original one is in the first the post and now its the one that you sent me.

Leesha

Reply | Quote

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;

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote