Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculation Fields - Bound and Unbound (Access 2000)

    I have two scenarios on a form with a subform that I'm having trouble getting right.

    1. I have a table with a "ProjectAmount" field and a "ChangeOrderAmount" field. There is a third "bound" field called "RevisedProjectAmount", which should be populated by the SUM of the "ProjectAmount" and the "ChangeOrderAmount" fields for that particular record. However, when I try to insert the expression =Sum([ProjectAmount]+[ChangeOrderAmount]) in the field on the form, it doesn't work properly--it seems to be totaling all the field amounts in the table, not just the amounts reflected for the one particular record.

    2. I have a second, related table that has a "BillingPercentage" combo-box field that lists percentages from 10 to 100 (in 10% increments). We want an "unbound" field on the subform that will always reflect the SUM total of "BillingPercentage" fields in the many related records on the subform (related to the main table/form's one primary key field). This unbound field should be called "TotalPercentage", which we need to monitor on the subform so that it eventually equals 100 and never goes over 100.

    Whoa! I just can't seem to create expressions that work properly in either scenario. Can someone please help me? Many, many thanks.

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

    Re: Calculation Fields - Bound and Unbound (Access 2000)

    1. Simply use

    =[ProjectAmount]+[ChangeOrderAmount]

    The Sum function in Access always computes across all records, so you don't want to use it here.

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

    Re: Calculation Fields - Bound and Unbound (Access 2000)

    2. Does the following do what you want?

    =Sum([BillingPercentage])

    If the combo box is formatted as a percentage, you'll have to format the text box that way too.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation Fields - Bound and Unbound (Access 2000)

    Oh, man! I can't believe I've been working on this expression for an hour and it's so simple. Thank you, Hans! You're always my hero!

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation Fields - Bound and Unbound (Access 2000)

    Hi again, Hans,
    No, sorry, I must be doing something wrong. I have a subform on the main form. I tried but I can't get an unbound field in the subform, so I tried putting it on the main form but it doesn't work--it gives me an "error" message. For example, for one primary field record, have 4 subform records reflecting 10, 30, 20, and 10 billing percentages (these numbers are related to other fields in the subform). So I want an unbound box somewhere on the form that will reflect an ongoing total of the many related records "BillingPercentage", in this case the total reflected would be 80. I'd appreciate any help you can give me. Thank you...Mary

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

    Re: Calculation Fields - Bound and Unbound (Access 2000)

    The text box should be on the subform, for example in the form header or footer. If you wish, you can hide it by setting its Visible property to No, and create a text box on the main form with control source

    =[NameOfSubForm]![NameOfTextBox]

    where NameOfSubForm is the name of the subform as a control on the main form, and NameOfTextBox is the name of the hidden text box on the subform.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation Fields - Bound and Unbound (Access 2000)

    Hans, could my problem be that the form was created with AutoForm, so there is no separate subform, it's inserted as the table itself? Will I need to create separate main and subforms to replace the autoform in order to get this calculation to work?

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

    Re: Calculation Fields - Bound and Unbound (Access 2000)

    If the Source Object of the subform is Table. followed by the name of the table, you'll have to delete this subform, and create a "real" subform, then insert it into the main form. You can use one of the wizards to create the subform, but you'll have to edit the design manually to create the text box in the form header or footer.

  9. #9
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation Fields - Bound and Unbound (Access 2000)

    Makes sense, Hans...Many, many thanks for all your help. I'm sure once I get the "real" subform in place, there won't be the problems. Until next time...Mary

  10. #10
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation Fields - Bound and Unbound (Access 2000)

    Oh, Hans, the expression you gave me works perfect, but I just noticed that the sum results are not being populated in the underlying table for the revised field. Is there something special I have to do to make the expression work for a "bound" control, rather than an unbound control? Thanks!...Mary

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

    Re: Calculation Fields - Bound and Unbound (Access 2000)

    I don't understand - the sum of the BillingPercentage values is a calculated value, it shouldn't be stored in a table.

  12. #12
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation Fields - Bound and Unbound (Access 2000)

    So sorry, Hans, I didn't make myself clear. The BillingPercentage is fine as an unbound control for us to monitor on the form; however for Scenario #1, we wanted the sum of the two fields to appear in a third field in the table. If it's not possible to put a calculated field in a bound control, is there another way around it that you know of? Do I need to do it in a query first? Thank you!

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

    Re: Calculation Fields - Bound and Unbound (Access 2000)

    I apologize, I didn't take notice of which post you were replying to.
    However, RevisedProjectAmount shouldn't be stored in the table either because it's derived information. Instead, create a query based on the table that returns all the fields plusr RevisedProjectAmount as a calculated field:

    RevisedProjectAmount: [ProjectAmount]+[ChangeOrderAmount]

    You can use this query as record source for forms and reports, and to base further queries upon.

  14. #14
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation Fields - Bound and Unbound (Access 2000)

    Very good, Hans. I'll do just that, and thank you again for all your help! Much appreciated!...Mary

Posting Permissions

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