Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2006
    Posts
    108

    Unanswered: continous form totals (unbound textboxes)

    EDIT: sorry guys i was in a rush yest. before i had to leave. here is the ?? agian:

    i have a calculated field the multiplies the qty and the unitcost per record on my tabular form.

    Im trying to get a field in the footer that will give the total cost of each record shown on the tabular form. (which is filtered)

    Agian, how do i go about doing this? all access help tells me, is to drag the field to the footer which didnt work.
    Last edited by jwalker343; 08-31-07 at 16:17.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If the calculated field is stored In the control source property for the field in the footer put =sum([mycalculatefieldname]) or try putting another field in the footer =sum([name_of__other_field_in_footer_which_calculates]) or try putting a copy of both fields in the footer and calculate on the field names of the copy of the fields. Not sure I understand fully what you're trying to do though so this is probably an obscure answer - sorry about that.
    Last edited by pkstormy; 09-02-07 at 00:35.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jul 2006
    Posts
    108
    I understand here is a better example:

    I have a form that has 2 fields [qty] and [Unitprice] then i have another field that is unbound [totalprice] and calculates [qty]*[unitprice] to get a total price per part

    however im trying to get the total amount of the records that are shown


    see attatched image for visual explination
    Attached Thumbnails Attached Thumbnails untitled.bmp  

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Overall summed value

    jwalker343,

    I created a table with a qty and a price field and put these fields on a continuous form. In the form's footer I added a field which has a control source =sum([qty]*[price]). This gives me the overall total price of all the qty*price fields. The difference though is that the qty and price fields on my continuous form are bound where I see in your bmp that they are dlookups (which makes a difference.) As you've probably already guessed, having dlookups is what's giving you problems using the =sum([qty]*[price]) in the footer. Do you need to use dlookups on your form? This seems a tad bit extreme with all the dlookup fields for the form. I think if you could somehow makes the qty and price fields bound to a query/table, you would have success using the =sum([qty]*[price]) in the footer.

    Have you considered having a summation query with a dlookup to that value? I'm guessing you already have and found that this gives you problems also as you need to dlookup values to get qty and price. I'm also guessing you've probably already attempted to get rid of the dlookups somehow.

    (not that this is a good arguement for storing the calculated value in the table) but have you considered doing this since you're using a lot of dlookups on the form - might make your life a little easier with the dlookups. Overall though, I think you need to find a way to avoid having all the dlookups for all the fields on the form.

    If you can zip an extract, I can probably find a way to help you avoid all the dlookups.

    If it helps at all, here's the example I quickly made (see form2) but it doesn't use dlookups on the form as your bmp illustrates. I'll play around with one which utilizes dlookups as you've peaked my curiosity calculating totals on dlookup fields.
    Attached Files Attached Files
    Last edited by pkstormy; 09-04-07 at 19:16.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Jul 2006
    Posts
    108
    hey i took your advice and bound the fields to a table. it now takes slightly more work to input the data but it works. im still doing a calculation on qty*price per record and then i used =sum (qty*price) to show the total for the form and it worked flawlessly.

    i never in my life would have known that you couldnt total dynamic fields, or dlookups.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Thanks for the reply jwalker343. Sorry to hear about the more work part on inputing data but I'm glad to hear the sum works. I'm still going to look into the dlookups totaling as I'm curious but thanks again for letting me know.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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