Need help with access 2010

[vkumar]

I have two tables Invoice and payment. In the invoice table I have job number,
Invoice number and Invoice amount, and in the payment table I have Job number,
invoice number and payment. I would like to do an Outstanding invoice subform
in the payment form, where it will show all the outstanding invoices as soon as
you enter the job number.

My subform shows the info only for the invoices that got paid in full or partial
payments. If the invoice never got paid it is not showing up in the list, since
I have the query results joining the two tables (invoice and payment). How to


bring all the records from the invoice table minus payments, even if there was
no payments on an invoice. Thank you for the help.

[pbaldy]

Right-click on the join line in the query to edit it. Choose the appropriate "include all records from...". In SQL view, that will change INNER to LEFT or RIGHT as appropriate.

[vkumar]

Thank you, It worked.
I have another problem in the same query. When the invoices didn't have any payment, the query result had null in the payment. And for the invoice balance also it did not show the invoice balance if ii did not have any payments. It returned Null, eventhough there was an Invoice amount. If the invoices had any payments it showed the correct amount. Please help.




QUOTE=pbaldy;129002]Right-click on the join line in the query to edit it. Choose the appropriate "include all records from...". In SQL view, that will change INNER to LEFT or RIGHT as appropriate.[/QUOTE]

[pbaldy]

Try the Nz() function around that field.

[vkumar]

I tried. It did not work. I used it in IIF fx
This is what I have
Balance: [Invoice Query]![Adjusted Invoice Amount]-[Invoice Query]![Sum Of Amount Received] - it gives correct answer, but it leaves the balance field empty if there is no payment. The rest are correct.

I tried like this:
Balance:iif(([Invoice Query]![Sum Of Amount Received]=Nz()), [Invoice Query]![Adjusted Invoice Amount],([Invoice Query]![Adjusted Invoice Amount]-[Invoice Query]![Sum Of Amount Received]))

And this:
Balance:iif(([Invoice Query]![Sum Of Amount Received]=NULL), [Invoice Query]![Adjusted Invoice Amount],([Invoice Query]![Adjusted Invoice Amount]-[Invoice Query]![Sum Of Amount Received]))

[pbaldy]

Did you check in help for the proper usage of the function?

[vkumar]

I am sorrry I don't understand what you are asking. I think my iif fx lo oks correct. If not please tell me where I am wrong. Instead of nulls, I want Zero in my sum of amount received and the invoice balance should be the invoice amt if there is no payments on that invoice. Thank you for trying to help.

[vkumar]

I got it and I understood what you asked. It is working now. Sorry for being so dumb.
Thank you again for your help.

Did you check in help for the proper usage of the function?

[pbaldy]

Happy to help.