Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculating from a Calculated Field (2002, sp2)

    I sure miss the search feature! It's likely someone has posted similar questions before.

    I have a main form with two subforms. The main form has student information, the first subform has candy to be sold, the third has the student's payments.

    In the candy form there is field called Total, which is the quantity to each candy X the price. That field is calculated through the underlying query. Then there is a total at the bottom, which is a field with a sum function in it.

    The payment form has dates and payments from the students.

    I want to total that at the bottom, too, then find the difference between those two calculated totals. Nothing I've tried so far has even gotten past the "Name?" error~
    egghead

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

    Re: Calculating from a Calculated Field (2002, sp2)

    Referring to calculated controls in another subform is always tricky. It's probably better to calculate the amount on the payment subform using DSum. You have to restrict the sum to the student shown in the main form. I don't know the names in your database, but the control source of the text box to total the candy amounts would look like this, with the appropriate names substituted.

    =DSum("[Quantity]*[Price]", "tblStudentCandy", "StudentID=" & [StudentID])

  3. #3
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating from a Calculated Field (2002, sp2)

    Thank you for your quick reply! I haven't used Dsum before, so I fiddled with it a bit, but after I typed in the expression the way I thought it should be, I switched to form view, which showed nothing. When returning to design view, my expression was gone???

    I have atttached a screen shot of my form. There you can see my two calculated totals on the two forms (candy and payment). I need to calculate their difference for a balance due amount.
    Attached Images Attached Images
    egghead

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

    Re: Calculating from a Calculated Field (2002, sp2)

    You may have forgotten to press Enter or click in another control before switching to form view. Access has an annoying habit of canceling changes to a property if you forget that.

  5. #5
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating from a Calculated Field (2002, sp2)

    Yep, I forgot to deselect the control. But now I get "#Error", so I'm only a litter farther ahead! <img src=/S/blackhole.gif border=0 alt=blackhole width=15 height=15>

    On the footer of the main form I created an unbound control and entered this expression: =DSum("[quantity] * [price]","qryOrder Query","student id=" & [student id])
    egghead

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

    Re: Calculating from a Calculated Field (2002, sp2)

    You have a space in the "Student ID" field. If that is correct, you MUST enclose the field name in square brackets:

    =DSum("[quantity] * [price]","qryOrder Query","[student id]=" & [student id])

  7. #7
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating from a Calculated Field (2002, sp2)

    Still getting the same error. I've attached a different screen shot to see if you notice anything else.

    Thank you so much for the help you've already given. I always amazed at the things this program is capable of!
    Attached Images Attached Images
    egghead

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

    Re: Calculating from a Calculated Field (2002, sp2)

    1. Are all fields mentioned in the expression ([quantity], [price] and [student id] present as fields in [qryOrder Query]?
    2. Is [student id] present as a field in the record source of the main form? It might help to put a text box bound to [student id] on the main form; it can be invisible.
    3. Is [student id] numeric? If it is a text field, you must add quotes in the Where condition:

    =DSum("[quantity] * [price]","qryOrder Query","[student id]=" & Chr(34) & [student id] & Chr(34))

    Chr(34) is the code for the double quote character.

  9. #9
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating from a Calculated Field (2002, sp2)

    1. Yes, all the fields are in the Order Query.
    2. Student ID is the primary key field of the Students table, which is the basis of the main form. It's an autonumber field.
    3. Student ID from Students is related to Studen ID in the Order Query. It is numeric.
    egghead

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

    Re: Calculating from a Calculated Field (2002, sp2)

    If you still get #Error in the text box, there must be a typo somewhere; I've got similar expressions in comparable situations that work without problems. Please check and recheck everything. If you still can't find it, simplify the expression to see when it works and when it doesn't. This may help to localize the problem. For example:
    - Without Where condition and multiplication:
    =DSum("[price]","qryOrder Query")
    - Without the multiplication:
    =DSum("[price]","qryOrder Query","[student id]=" & [student id])
    - Without the Where condition
    =DSum("[quantity] * [price]","qryOrder Query")

  11. #11
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating from a Calculated Field (2002, sp2)

    You saw where I had the expression: in the footer of the form? Is there anything wrong with the placement of the subforms?

    I will recheck the expression, too. I'll have to do that tomorrow. Thank you!
    egghead

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

    Re: Calculating from a Calculated Field (2002, sp2)

    The placement of the subforms and of the text box shouldn't matter.

  13. #13
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating from a Calculated Field (2002, sp2)

    I ended up going a different route, and got it to work eventually.

    I named the sum field for the candy "Student Total" and the sum field for the payments as "Student Payments". Then on the main form, not in the footer, I added an unbound control and entered the following in the control source:

    =[Order Query Subform]![Student Total]-[Payments Subform]![Student Payments]

    Thanks for all the help; I learned a lot about DSum!
    egghead

Posting Permissions

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