Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Aug 2002
    Location
    Des Moines, Iowa, USA
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculated fields in a report (XP)

    I am trying to create a report of monthly commission values, but having a difficult time getting the commission value to calculate.

    Every transaction has a broker involved in the buying and selling side of a transaction, therefore, the commission value = payment amount / 2 (half the commission goes to the buyer, half to the seller).

    On the report, I created a field called dblCommission. Within the Control Source property, I go to the Expression Builder and create the expression =[tblPayments]![dblPayment] / 2. When I try to run it, I get a prompt for tblPayments. When I change the expression to =tblPayments ! dblPayment, same thing. When I take out tblPayments out of the expression, all my values on the report show up as #Error. What is happening??? I know it has to be something basic, I'm just not seeing it.

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

    Re: Calculated fields in a report (XP)

    Is dblPayment a field in the record source of the report?

    If so, =[dblPayment]/2 should work, unless dblPayment could be a field in more than one table in the record source. This can happen if the record source is a qwuery based on more than one table. In that case, try =[tblPayments].[dblPayment]/2 using a point <font face="Georgia">.</font face=georgia> as separator, not a bang <font face="Georgia">!</font face=georgia>.

    If dblPayment is *not* in your record source, you should probably add it.

  3. #3
    Star Lounger
    Join Date
    Aug 2002
    Location
    Des Moines, Iowa, USA
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated fields in a report (XP)

    Yes, dblPayment is part of the record source. When I edit the value in the Control Source property from dblPayment to [dblPayment] / 2, and then run it, all the values show #Error. I tried to recreate the report, thinking maybe I ran into a Microsoft "feature", but got the same result. At least it's consistent.

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

    Re: Calculated fields in a report (XP)

    1. Make sure your control source is

    =[dblPayment]/2

    and not just [dblPayment]/2

    2. Check very carefully if there is a possible risk of confusion over control names. If you have another control named dblPayment (not the text box bound to the field dblPayment), Access gets confused.

  5. #5
    Star Lounger
    Join Date
    Aug 2002
    Location
    Des Moines, Iowa, USA
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated fields in a report (XP)

    I must've had something syntactically wrong. I recreated the field, and it all seems to work well. I also experimented with making the field come from a query where it the field is already calculated, and that seems to work also.

    Thank you for your help! I think I probably forgot the = sign. I knew it had to be something basic. Have a great weekend!!

Posting Permissions

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