Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query off results of suform getting populated (Access 2000 )

    OK, this a little wierd to explain. I have a subform which is based on a query (call it Query A) that gets its data from 2 tables and a query about an overdue invoice. This works great. It gets everything I need EXCEPT the payments made towards the invoice. Since most invoices will not have any payments made yet,( is then null) , I cannot put payment details in the same query. (query A) . Therefore, I am trying to have a textbox in the subform to equal the value of another query. (query [img]/forums/images/smilies/cool.gif[/img] I want this query to be based on the InvoiceID that is returned from query A. The subform is in a continues form (format). How can I reference the InvoiceID on the subform for each row that exists in the subform?

    My query B is:
    SELECT Sum([Payments].[PaymentAmount]) AS TotalPayments, [InvoiceID]
    FROM Payments
    WHERE ((([Payments].[InvoiceID])=[Forms]![FRM_PaymentProcessing]![FRM_FindOverdueAccountsforPaymentProcessingSubform].[InvoiceID]))
    GROUP BY [InvoiceID];

    Here is a picture of the form:

    Thanks
    Attached Images Attached Images

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

    Re: Query off results of suform getting populated (Access 2000 )

    This is just off the top of my head, I haven't tested it. Create a query based on Query A and Query B (but without the WHERE condition), with an outer join between them on InvoiceID (double click the join and select the option to return ALL records from Query A). Use this new query as records source of the subform.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query off results of suform getting populated (Access 2000 )

    You absolutly rock! That works awesome. One minor thing. If a payment column is blank, I cannot set the default value to zero, can I? Eventhough I have the default property value set to zero, it still shows blank if an invoice has no payments made towards it.

    Again thanks for the solution [img]/forums/images/smilies/smile.gif[/img]

    Shimmer

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

    Re: Query off results of suform getting populated (Access 2000 )

    You can replact the TotalPayments field in the query by a calculated expression
    Payment: Nz([TotalPayments], 0)
    Substitute the appropriate names. The Nz function returns the first argument, unless that is empty (null), in that case it returns the second argument.

Posting Permissions

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