locked
DLookUp Referencing a Query To Get a Total Amount Owed RRS feed

  • Question

  • I have a form named: Branch 142 Membership that has a text box named: TextBoxTotalAmountDueToDate. The control source is: =DLookUp("[Total Amount Due]","[QryTotalAmtDueBy]","[MemberID]=" & [Forms]![Branch 142 Membership]![MemberID])

    I'm getting the sum total for all of the records in my database that is the sum for just one member. There's one payment record in the Payments table. Of course when I open the query I get the total for the one payment record.

    I want to get the Sum([PPUnionDuesAmountNoDeduct]-[PaymentAmount]) for each record in the object: TextBoxTotalAmountDueToDate.  If the amount is zero I want the text box to display zero. When I entered =Sum([PPUnionDuesAmountNoDeduct]-[PaymentAmount]) I got an error in text box.

    Thanks for the help.

    Monday, February 2, 2015 4:05 PM

Answers

  • I recommend that you develop the entire solution within the query

    Then apply the DLookUp method for just that - look up....

    Don't attempt to perform math functions as part of the DLookUp definition; it is so much easier to trial things in the query to fine tune them...

    So you have this query: QryTotalAmtDueBy

    which is using criteria from your open form: [Forms]![Branch 142 Membership]![MemberID])

    that query needs to run ok just stand alone

    Am a little unclear about: the sum of [PPUnionDuesAmountNoDeduct]-[PaymentAmount] ; if you just mean the 'result of' then this is a simple calculated value....but if you mean the sum of the entire record set then you need an aggregate query as a 2nd query. 

    But in any case work your query(s) until it displays the correct result - then DlookUp that value to display it in your form.

    Monday, February 2, 2015 11:40 PM
  • well the syntax is:

    DLookUp("fieldname","queryname", Whereclause)

    for sanity checking purposes - make a query that has only one record result and set it up just as:

    DLookUp("fieldname","queryname")

    to be sure that works....

    Then work on the whereclause separately...yours appears ok presuming Member ID is a number field type...but the whereclause varies if it is a text field, date field, etc...for which there are many Q/A at forums that reviews this topic

    Tuesday, February 3, 2015 5:36 PM
  • if your query has only 1 record - then you don't need the where clause

    between the quotation marks is literal so you don't use the brackets

    this should work:

    =DLookUp("Total Amount Due","QryTotalAmtDueBy")

    Tuesday, February 3, 2015 11:55 PM
  • Are you suggesting I add: Nz(DLookUp("[Total Amount Due]","QryTotalAmtDueBy","MemberID=" & [MemberID]),0) AS TotalDue before the following?

    FROM (Members LEFT JOIN [Union Meeting Attendance] ON Members.MemberID = [Union Meeting Attendance].UMAID) LEFT JOIN [Grievance Tracking System] ON Members.[Social Security Number] = [Grievance Tracking System].[Social Security Number]
    ORDER BY Members.LastName, Members.FirstName, Members.MiddleName;

    Yes.  It should be in the SELECT clause, separated from whatever is currently the last column in the clause by a comma.  

    To use a bound column in the form, rather than calling the Dlookup function, you would LEFT JOIN the [Union Meeting Attendance] table to QryTotalAmtDueBy on MemberID in your current query.  The Total Amount Due column could then be returned in the query and a text box in the form bound to it.  The query would not be updatable, however, so you would not be able to edit data in the form.   For this you must call the Dlookup function, either in a computed column in the query, or in a computed control in the form.

    I should point out that using a computed column in the query or a computed control in the form should both give the same result, so I can't explain why you are currently getting the same total due value for each record.

    The MemberID and SocialSecurity Number columns are both candidate keys, so joining tables on either should return the same results.


    Ken Sheridan, Stafford, England

    Monday, February 9, 2015 9:48 PM

All replies

  • I recommend that you develop the entire solution within the query

    Then apply the DLookUp method for just that - look up....

    Don't attempt to perform math functions as part of the DLookUp definition; it is so much easier to trial things in the query to fine tune them...

    So you have this query: QryTotalAmtDueBy

    which is using criteria from your open form: [Forms]![Branch 142 Membership]![MemberID])

    that query needs to run ok just stand alone

    Am a little unclear about: the sum of [PPUnionDuesAmountNoDeduct]-[PaymentAmount] ; if you just mean the 'result of' then this is a simple calculated value....but if you mean the sum of the entire record set then you need an aggregate query as a 2nd query. 

    But in any case work your query(s) until it displays the correct result - then DlookUp that value to display it in your form.

    Monday, February 2, 2015 11:40 PM
  • Thanks for your response. The control source: =DLookUp("[Total Amount Due]","[QryTotalAmtDueBy]","[MemberID]=" & [Forms]![Branch 142 Membership]![MemberID]) is not working properly. Every record in my database is showing the same amount owed by one person $122.25.

    I've tried searching the internet to find the correct syntax to use when referencing a query. Using your example of a query: QryTotalAmtDueBy, what is the correct syntax to enter in the control course for TextBoxTotalAmountDueToDate which is using the criteria when I open the form: [Forms]![Branch 142 Membership])?

    I mentioned the sum of [PPUnionDuesAmountNoDeduct]-[PaymentAmount] because that's all I'm interest in for each record. I already have an aggregate query as a 2nd query.

    Thanks

    Tuesday, February 3, 2015 5:21 PM
  • well the syntax is:

    DLookUp("fieldname","queryname", Whereclause)

    for sanity checking purposes - make a query that has only one record result and set it up just as:

    DLookUp("fieldname","queryname")

    to be sure that works....

    Then work on the whereclause separately...yours appears ok presuming Member ID is a number field type...but the whereclause varies if it is a text field, date field, etc...for which there are many Q/A at forums that reviews this topic

    Tuesday, February 3, 2015 5:36 PM
  • My query has only one record. I entered in the text box: TextBoxTotalAmountDueToDate control source: =DLookUp("[Total Amount Due]","[QryTotalAmtDueBy]","[MemberID]"). I still get the result for that one record in the QryTotalAmtDueBy showing for each record in the database.

    "[Total Amount Due]" is a field from the "[QryTotalAmtDueBy]". It is the sum for each person all of the [PPUnionDuesAmountNoDeduct] minus all of the [PaymentAmount] for that person.

    My problem is as I see it is that the DLookUp is not resolving to the individual record. I don't understand why the control source is not resolving to the specific MemberID when I'm open the form: Branch 142 Membership. The MemberID field is an AutoNumber data type.

    Thanks

    Tuesday, February 3, 2015 6:28 PM
  • if your query has only 1 record - then you don't need the where clause

    between the quotation marks is literal so you don't use the brackets

    this should work:

    =DLookUp("Total Amount Due","QryTotalAmtDueBy")

    Tuesday, February 3, 2015 11:55 PM
  • Sorry for taking so long to reply, I've been away on business. I do not know how to explain this other than all 3,000 records are show the same $122.25 amount owed for the one individual that actually owes $122.25. That individual is the only record in the table and thus is the only record in the query. Each of the 2,999 records should show that he/she owes $0.00, not $122.25.

    So, I assume the problem is the where clause of the: =DLookUp("[Total Amount Due]","[QryTotalAmtDueBy]","[MemberID]=" & [Forms]![Branch 142 Membership]![MemberID])

    Thanks for the help.

    Robert

    Sunday, February 8, 2015 1:23 PM
  • your  Feb 3 post states your query has 1 record.  Your most recent post confusingly describes 3000 records.

    the DLookUp is for 1 value.  If a query has 1 record then you don't need a Where clause in the DLookUp.  If the query (or table) has multiple records - then one must of course tell it which record to look up using the Where clause.  So that is that.

    If you are saying your query results in 3000 records for which a repeating value of $122.25 is incorrect - that is an entirely different topic.  That would be due to a query design error, most likely an incorrect join or possibly no join.

    But things are unclear.

    Monday, February 9, 2015 12:33 AM
  • I will try again. For example, I have a table (Payments) that has 5 records. The 5 Payments table records has $24.45 entered all for the same person, Billy Bob that totals $122.25. I have a table (Members) that Billy Bob is one of 10 records. The query:QryTotalAmtDueBy returns 1 result (record) and it is only for Billy Bob for $122.25 because he is the only one entered in the Payments table. Total Amount Due: Sum([PaymentAmount]-[PPUnionDuesAmountNoDeduct])

    When I enter into the control source for text box: TextBoxTotalAmountDueToDateTab5 the expression: =DLookUp("[Total Amount Due]","[QryTotalAmtDueBy]","[MemberID]=" & [Forms]![Branch 142 Membership]![MemberID]) on the form: Branch 142 Membership. I get the $122.25 total amount due for Billy Bob and for the remaining 9 records in the TextBoxTotalAmountDueToDateTab5.

    You suggested I try =DLookUp("Total Amount Due","QryTotalAmtDueBy"). I get #Error when I entered it in the text box: TextBoxTotalAmountDueToDateTab5. I modified what you gave me and entered: =DLookUp("[Total Amount Due]","[QryIndivTotalAmtDueBy]"). I still get the $122.25 total amount due for Billy Bob and for the remaining 9 records in the Members table.

    I don’t care if I use a control source that references the Total Amount Due from the query QryTotalAmtDueBy or if I use a control source expression that is the sum of the PaymentAmounts minus the sum of the PPUnionDuesAmountNoDeduct for each record in the Members table. I've tried: =IIf(IsNull([MemberID]),0,DSum("[PPUnionDuesAmountNoDeduct]-[PaymentAmount]","Payments","[MemberID]=" & [MemberID])). I get nothing for any record in the Members table.
    Monday, February 9, 2015 7:29 AM
  • Have you tried returning the value as a computed column in the form's RecordSource query?

    SELECT etc,
    Nz(DLookUp("[Total Amount Due]","QryTotalAmtDueBy","MemberID=" & [MemberID]),0) AS TotalDue
    FROM etc;

    Does the query return the correct amounts per member if opened independently?  If so you can bind a text box control in the form to the TotalDue column.

    BTW if the form does not need to be updatable performance would be far better using a LEFT OUTER JOIN than by calling the DLookup function.  Only use the latter if the form needs to be updatable.

    Ken Sheridan, Stafford, England

    Monday, February 9, 2015 1:47 PM
  • Ken the query returns the correct amount per member - which is one member, me. I'm testing the calculated total amount due. I have 5 records in the Payments table. All of them are for me. I have not tried returning the value as a computed column in the Branch 142 Membership form's query (Membership_Temp Query) because I don't know the proper syntax to do that. Actually, I thought I was binding to the text box the Total Amount Due field from the query (QryTotalAmtDueBy). Please point me to example if you can and I will try to do it.

    Are you suggesting I add: Nz(DLookUp("[Total Amount Due]","QryTotalAmtDueBy","MemberID=" & [MemberID]),0) AS TotalDue before the following?

    FROM (Members LEFT JOIN [Union Meeting Attendance] ON Members.MemberID = [Union Meeting Attendance].UMAID) LEFT JOIN [Grievance Tracking System] ON Members.[Social Security Number] = [Grievance Tracking System].[Social Security Number]
    ORDER BY Members.LastName, Members.FirstName, Members.MiddleName;

    BTW as you can see from the above segment of the SQL for the Branch 142 Membership form I still have references to Social Security Number. How many problems will it cause if I replace Social Security Number with MemberID?

    The Branch 142 Membership form can be updated from the Grievance Tracking form.

    Thanks for the help.

    Monday, February 9, 2015 7:00 PM
  • Are you suggesting I add: Nz(DLookUp("[Total Amount Due]","QryTotalAmtDueBy","MemberID=" & [MemberID]),0) AS TotalDue before the following?

    FROM (Members LEFT JOIN [Union Meeting Attendance] ON Members.MemberID = [Union Meeting Attendance].UMAID) LEFT JOIN [Grievance Tracking System] ON Members.[Social Security Number] = [Grievance Tracking System].[Social Security Number]
    ORDER BY Members.LastName, Members.FirstName, Members.MiddleName;

    Yes.  It should be in the SELECT clause, separated from whatever is currently the last column in the clause by a comma.  

    To use a bound column in the form, rather than calling the Dlookup function, you would LEFT JOIN the [Union Meeting Attendance] table to QryTotalAmtDueBy on MemberID in your current query.  The Total Amount Due column could then be returned in the query and a text box in the form bound to it.  The query would not be updatable, however, so you would not be able to edit data in the form.   For this you must call the Dlookup function, either in a computed column in the query, or in a computed control in the form.

    I should point out that using a computed column in the query or a computed control in the form should both give the same result, so I can't explain why you are currently getting the same total due value for each record.

    The MemberID and SocialSecurity Number columns are both candidate keys, so joining tables on either should return the same results.


    Ken Sheridan, Stafford, England

    Monday, February 9, 2015 9:48 PM
  • I want to apologize for the delay in responding. I was hospitalized for pneumonia. I added the following to SQL statement per Ken’s suggestion:

    Nz(DLookUp("[Total Amount Due]","QryIndivTotalAmtDueBySSN","MemberID=" & [MEMBERS.MemberID]),0) AS [Total Amount Due]

    I had to modify Ken’s by adding a reference to the Members table. I got an error message that ‘[MemberID]’ could refer to more than one table listed in the FROM clause of my SQL statement. Spent 2 days trying to understand the error message. Kept looking for the reference in the FROM clause. Naming problems I created in 1995.

    It worked to some degree. However, it gave me zero for each record including the one record that should have $122.25. I’ll try another post since this one is posted an answered.

    I think I finally understand what Ken means when he asked if the text box object on the form needs to be updatable. If he means will a user have a need to enter data, the answer is no.

    Thanks to everyone who responded.
    Thursday, March 5, 2015 6:20 PM