Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    NZ function in a query (AccessXP)

    I think I need to use the NZ function in a query. I have two tables one holding the invoiced amounts (tblMonthlyInvoice) and one with payments (tblPayments). I have included both on a query grid and have joined them by the invoice number. Of course that shows only the invoices that have payments. What I need to be able to see is those plus the invoiced amounts that have no payments. I think I need to use the NZ function but can find only information for VBA. If there is no PaymentAmount for an invoice I want the NZ function to return "No payment received". Nothing I have tried has worked.

    Any ideas or references would be appreciated. Thanks.

    Thank you for your help.

    Fay

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: NZ function in a query (AccessXP)

    You don't need the Nz function for this. You must change the join between the tables. Double click the line joining the tables, and select the option to Display all records from tblInvoices; depending on the way you have created the join, this is option 2 or 3. This is called an outer join, whereas option 1 is called an inner join.

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: NZ function in a query (AccessXP)

    What Hans says is correct. However, I guess that what you have come up against is that you can see an invoice amount but do not get any data for payments. Setting the outer join may not solve the problem as there are probably no records in the payment table.

    A way to work around this is that when you input an invoice in tblMonthlyInvoice, have the program put the related data in tblPayments with a zero amount. Your payment amount will then function. If you are using a form to input invoices and use a save button to save the data, you could also use an insert into statement to insert the data in tblPayments automatically. You could then either show zero as payment received or where there is a zero, input No Payment Received.

    Is it possible to have more then one payment on an invoice?

    HTH
    Regards,

    Gary
    (It's been a while!)

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NZ function in a query (AccessXP)

    Thanks Gary and Hans.

    I got it to work with the second join type.
    Gary it is possible to have multiple payments for an invoice. I will stay with the simple solution Hans came up with.

    Thanks gentlemen. Fay

Posting Permissions

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